# Need help with This Date Formula

• 0
• Question
• Updated 3 years ago

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")

• 3,152 Points

Posted 3 years ago

• 0
• 70,464 Points
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")
• 3,152 Points
Thanks a lot.
• 3,152 Points
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.
• 70,464 Points
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")
• 3,152 Points
Mark, this is a great piece of work.  Absolutely works in the  manner I envisaged.  This VAR business is interesting.  Learning from you.
• 70,464 Points
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.