Creating status based on date

navypoint16

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!

Thableaus

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,