Discussions

 View Only
  • 1.  Summary not capturing second week of project

    Posted 03-23-2018 15:31
    The powers that be want me to convert this Excel Report to a "live" QB Report and while is is far easier in Excel, I cannot get this 100% in QB.
    The Correct Total for the Gulf Region is 18 for the week of 5/7 and this formula arrives at the correct answer: (A Project can last multiple weeks and these are designated by [WK1 Advantage], [WK2 Advantage] etc...)

    If([WK1 Advantage]>=ToDate("May 7,2018") and
    [WK1 Advantage]<ToDate("May 14,2018"),"05-07",
    If([WK2 Advantage]>=ToDate("May 7,2018") and
    [WK2 Advantage]<ToDate("May 14,2018"),"05-07",
    If([WK3 Advantage]>=ToDate("May 7,2018") and
    [WK3 Advantage]<ToDate("May 14,2018"),"05-07"

    The Summary Report is correct...



    However, when I add in the preceding week 04.30... the week of 05.07 no longer calculates any of the fields past [WK1 Advantage]

    If([WK1 Advantage]>=ToDate("Apr 30,2018") and
    [WK1 Advantage]<ToDate("May 7,2018"),"04-30",
    If([WK2 Advantage]>=ToDate("Apr 30,2018") and
    [WK2 Advantage]<ToDate("May 7,2018"),"04-30",
    If([WK3 Advantage]>=ToDate("Apr 30,2018") and
    [WK3 Advantage]<ToDate("May 7,2018"),"04-30",

    If([WK1 Advantage]>=ToDate("May 7,2018") and
    [WK1 Advantage]<ToDate("May 14,2018"),"05-07",
    If([WK2 Advantage]>=ToDate("May 7,2018") and
    [WK2 Advantage]<ToDate("May 14,2018"),"05-07",
    If([WK3 Advantage]>=ToDate("May 7,2018") and
    [WK3 Advantage]<ToDate("May 14,2018"),"05-07",

    How can I correct this, so I am capturing all the weeks?
    I am at a total loss of what I am doing wrong


  • 2.  RE: Summary not capturing second week of project

    Posted 03-23-2018 20:04

    is it possible that the properties for the QB App in question shows its beginning week "Sun" or "Mon" that is not the same as the beginning week in the excel report?

    If you have access to your app's properties, that may be a place to start.