Count number of days between two dates and return the difference and if early or late?

LouiseM

Hi there Smartsheet community,

I don't know if anyone can help me?

I am trying to work out a formula to calculate the number of days between 2 dates, and also return if this was early, late or on-time.

For example:

Column A- Start date (20/06/2018)

Column B - End date (31/05/2018)

Column C - 20 days Early

 

OR

Column A- Start date (20/06/2018)

Column B - End date (10/07/2018)

Column C - 20 days Late

and so on.

 

I do have an excel formula which works this out, however I can't find a way for it to work in Smartsheet:

=IF(COUNT(A2,B2)=2,TEXT(B2-A2,"0 ""Day(s) Late"";0 "" Day(s) Early"";""On Time"""),"")

 

This is as far as i have got:

=([Date1]1 - [Date2]1)

(As you can see I am no formula guru by any means.)

 

I just can't work out how to get the text functions to return "Early" "Late" or "On-Time".

 

Does anyone know of a way to do this?

Paul Newcome

=IF(ISBLANK(End@row), "", IF(AND(ISBLANK(Start@row), ISDATE(End@row)), "Start Date Missing", IF(Start@row - End@row < 0, ABS(Start@row - End@row) + " Days Late", IF(Start@row - End@row > 0, ABS(Start@row - End@row) + " Days Early", IF(Start@row - End@row = 0, "On Time")))))

The bold Start and End will be replaced with your column names.

The "italicized" text between quotes will be whatever you want the cell to actually say.

 

Just be sure to leave the space between the opening " and Days. That will be the difference between "20Days Late" and "20 Days Late".

If the end date is blank, the cell will be blank. If the end date is entered without a start date, it will say "Start Date Missing". If the start date is before the end date, it will say "# Days Late". If the start date is after the end date it will say "# Days Early. Finally, if the start date and end date are the same, it will say "On Time".

LouiseM

Wow thank you Paul.

That is quite frankly amazing.

The logic you included will be a huge help with the dates if they are missing or fall before the end date, and is something I hadn't even thought about!

Thank you so much for your help and for putting in a way I (and any future smartsheet community users) could understand.

Thanks again

#Daymade

Paul Newcome

Always happy to help.