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!

[MarqueeCrew]

Yes! Would you post some sample data so that we can see your date formats?

Cheers,

Mark

[Thableaus · Accepted 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,

[navypoint16]

Yes - the data type for the date columns is "Date" and their format is "2019-03-05" (for today).

Thanks!

[MarqueeCrew]

@navypoint16,

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

[MarqueeCrew]

@navypoint16,

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