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

Why would an automation for movement not carry over a cell value?
Hi everyone, I’m running into an issue with one of my sheets and was hoping to see if anyone else has experienced this. Here’s the setup: I have a sheet where, when I check a box, the row moves to a “Completed” sheet. In my Total column, I use a formula that sums a group of cells tied together via a parent/child…
Unable to edit smartsheet forms without constant reboot
Is anyone having trouble with smarthsheet over the past several days? Every time I go into a form to edit it, the cursor jumps fields after each character is typed. It is so frustrating. In some cases the cursor gets frozen in a field and jumps back and forth between two fields and freezes the whole system. I have yet to…
Cannot move items
I want to move a sheet on my workspace to another folder but I am not getting the option to do so. I am the owner of the workspace, the sheet and the folder.