Date is Within the Next 5 Days

@emipathy

Hi Smartsheet Community,

I'm authoring a production schedule and one of the columns automatically populates status (complete, in progress, not started) based on a formula. 

For a specific row, I need it to display "In Progress" if the start date is within the next 5 days. 



Is this the correct formula for this? 

=IF([Start Date]34 < TODAY(+5), "In Progress", "Not Started")

Note: I've simplied this formula as if there were no other "IF" statements for example purposes. 

I'm having an incredibly hard time wrapping my head around this-- I've never been good with dates! Let me know if you have any tips. 

Cheers, 

Emi

Connor Hartford

Yes this is the correct formula.

Another way to make the formula more efficient is to include '@row' instead of the row number.

=IF([Start Date]@row < TODAY(+5), "In Progress", "Not Started")

Paul Newcome

Here is a tip for working with dates. Keep the YEARDAY function in mind. This assigns a number to the date depending on how far into the year said date is. SO January 1 would be the 1st day, February 1 would be 32nd day in the year, so on and so forth.

 

If you are having trouble visualizing it, then put the function somewhere on the sheet.

 

=YEARDAY([Column Name]@row)

 

You can then enter a date (or date generating formula) into the cell being referenced by the YEARDAY function and see where it falls in relation to the other dates in the sheet.

 

For example:

 

=YEARDAY([Start Date]@row)

 

will give you the corresponding number.

 

You can then plug in 

 

=YEARDAY(TODAY(5))

 

and make sure that is giving the expected result of being larger or in the future as compared to the Start Date.

.

So let's say your Start Date is 1 October 2019, and you want to see if that is in the next 5 days or less than today + 5.

 

=YEARDAY(DATE(2019, 10, 01))

 

yields 274

 

while 

 

=YEARDAY(TODAY(5))

 

yields 252 (based on the date of this post).

 

So now your formula can be read as 

 

=IF(274 < 252, "In Progress", "Not Started")

.

I hope all of that makes sense. I accidentally stumbled on that line of thinking, and it just clicked for me.

@emipathy

This helps out so much-- completely gets rid of my issue of wondering what dates are "greater than" or "less than" others. 

This will definitely help me visualize going forward! Thank you!