Need help with This Date Formula

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I am trying to establish a completion status of a project using finish field and then working out what has been completed, what is completed this week (7 days), what is due coming week and what is planned beyond that.    I tried  the following but apparently it does not like numbers. I am not too sure if I am using the right calculation for 7 days and beyond.  

If(

[Finish]< Today()-8, "Completed",

[Finish]< Today()-7, "Completed this week", 

[Finish]> Today() +7, "To be Completed Next Week",

[Finish]> Today() +8, "Planned")

 

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
You are trying to mix a date with a number and QuickBase won't be happy.  But also the sequence of those last 2 tests needed to be interchanged to get your logic right.

Try this

If(

[Finish]< Today()-Days(8), "Completed",

[Finish]< Today()-Days(7), "Completed this week",

[Finish]> Today() +Days(8), "Planned",

[Finish]> Today() +Days(7), "To be Completed Next Week")
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Need a little further help.  As my finish dates are falling on a Saturday, this formula incorrectly categorises when project was completed last week as it is working off the number of days.  I was hoping if this could work off the week which ends on a Saturday.
Assuming that your week starts on a Sunday and finishes on a Saturday you can try this

var date CurrentWeekSunday = FirstDayOfWeek(Today());


var date PreviousWeekSunday = FirstDayOfWeek(Today()) - Days(7);

var date NextWeekSunday = FirstDayOfWeek(Today()) + Days(7);

var date SecondNextWeekSunday = FirstDayOfWeek(Today()) + Days(14);

If(

[Finish]< $PreviousWeekSunday, "Completed",

[Finish]< $CurrentWeekSunday, "Completed Last Week", 

[Finish]< $NextWeekSunday, "Completed This Week", 

[Finish]< $SecondNextWeekSunday, "To be Completed Next Week", 

[Finish]>= $SecondNextWeekSunday, "Planned")
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Mark, this is a great piece of work.  Absolutely works in the  manner I envisaged.  This VAR business is interesting.  Learning from you.
Yes, using variable formuals in a formula greatly adds to it's readability and also can reduce repetition.  The names may only be the letters a-z and no spaces or numbers or special characters.