Hello - I'm looking to create a status column based on a formula referencing the current date and the date in another column.
Specifically, I have a "Start Date" column and an "End Date" column. I want to create a status column with the following parameters, but having trouble figuring out how to write them:
"Not Started" - if Current Date is before Start Date
"Green" - if Current Date is between Start Date and End Date
"Yellow" - if Current Date is between Start Date and End Date AND within two days of End Date
"Red" - if Current Date is after End Date
Is this possible through a formula tool?
Thanks very much in advance!
Solved! Go to Solution.
Hi @navypoint16
"Not Started" - if Current Date is before Start Date
"Green" - if Current Date is between Start Date and End Date
"Yellow" - if Current Date is between Start Date and End Date AND within two days of End Date
"Red" - if Current Date is after End Date
See if this works:
IF ToDate(DateTimeToday()) < [Start Date] THEN "Not Started"
ELSEIF ToDate(DateTimeToday()) >= [Start Date] AND DateTimeDiff([End Date], ToDate(DateTimeToday()), "days") <= 2 THEN "Yellow"
ELSEIF ToDate(DateTimeToday()) >= [Start Date] AND ToDate(DateTimeToday()) <= [End Date] THEN "Green"
ELSE "Red" ENDIF
@MarqueeCrew warned me about the time thing, so it's better to be sure you're comparing DATES with DATES. Being "Start Date" and "End Date" Date type Fields, not DateTime.
Cheers,
Yes - the data type for the date columns is "Date" and their format is "2019-03-05" (for today).
Thanks!
My formula looks like @Thableaus , but of course is unique to me.
IF DateTimeTrim(DateTimeStart(),"Day") < [Start Date] THEN "Not Started" ELSEIF DateTimeTrim(DateTimeStart(),"Day") >= [Start Date] AND DateTimeTrim(DateTimeStart(),"Day") <= [End Date] THEN "Green" ELSEIF DateTimeTrim(DateTimeStart(),"Day") >= [Start Date] AND DateTimeTrim(DateTimeStart(),"Day") <+ DateTimeAdd([End Date],2,"Days") THEN "Yellow" ELSE "Red" ENDIF
Cheers,
Mark
At first I wasn't going to say anything, but if you test with TODAY's date as the END Date you will see a reason to mark my solution as an "accepted solution". It isn't really even about that, but it doesn't hurt. When you compare today (2019-03-05 12:40:00) and 2019-03-05, you'll find that you come up with Yellow. The +2 will also become +1 for the same reason.
Lesson for all: Compare DATES with DATES. You can create a Date field instead of DateTimeStart() or DateTimeNow() in the formula or use the DateTimeTrim() function.
Cheers,
Mark