Date is Within the Next 5 Days

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

Find more posts tagged with

Publish

Education

Product Development

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…