Summary not capturing second week of project

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • In Progress
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
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

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.