If and Today functions

Missy Folkemer

Trying to do an if and statement with today function that shows a response if the date is < today, not started, greater or = to a date and less than end date, "Not Complete" but I keep getting unparseable

Paul Newcome

Try something like this:

=IF([Start Date]@row> TODAY(), "Not Started", IF([End Date]@row>= TODAY(), "Not Complete", "Complete"))

Ipshita

Hi @Missy Folkemer ,

You need to add another date type column next to your date column and name it Today in which just enter the formula for Today which is =TODAY() and then use the following formula -

=IF([Target Delivery Date]@row < TODAY@row, [Impact@row]:[Impact@row] = "High", 0)

where target delivery is your date column and Impact is your Status column, just make sure you have the status "Not Complete" in the column or the formula won't pick it up.

Cheers!

Missy Folkemer

I don't think I communicated it right. I have five steps in a process. Each step has it's own timeline, e.g. 1/18/23-2/15/23. If today's date is within the timeline of the phase, I want it to respond with what phase.

For example, if Client Engagement phase is 1/18-2/15, during that timeframe, I want the response to be "Client Engagement." Next is Discovery & Alignment and that phase is 2/22-3/1. When today's date is within that timeframe, I want the response to be "Discovery & Alignment." Next is Modeling and that timeframe is 3/6/23-3/31/23. If today is within, then it should say "Modeling." There are two more after that, but I think that gives you the idea. The timeframes are marked by columns "Start Date" and "End Date."

Paul Newcome

Are you able to provide a screenshot of your sheet so we can see the structure and indicate where exactly you are wanting to put the formula?

Missy Folkemer

I want the Current Status to auto populate based on the day and what phase of the project they are in. Phases are highlighted in blue.

Paul Newcome

Right. So the formula I posted above should work for the Current Status column.

Missy Folkemer

Could you give me more details on formula using the column names I have? Not sure if I'm following your example.

Paul Newcome

The formula I posted above is using your column names.

Genevieve P.

Just jumping in here - I agree with @Paul Newcome!

Have you tried copy/pasting his formula into your sheet, @Missy Folkemer? If so, are you getting an error or an incorrect result?

=IF([Start Date]@row> TODAY(), "Not Started", IF([End Date]@row>= TODAY(), "Not Complete", "Complete"))

It seems like you have a lot of blank cells, so I would maybe add in another statement at the beginning so this doesn't return any result:

=IF([Start Date]@row = "", "", IF([Start Date]@row> TODAY(), "Not Started", IF([End Date]@row>= TODAY(), "Not Complete", "Complete")))

Cheers,

Genevieve