Adding to dates without a formula

CycleBagEd

Hi folks.

So, today I discovered that if you are in a date column and type "+1" then it creates a date 1 day from today, and if you type "+15w" it creates a date 15 weeks from now.

If you just type "16w" you get the 16th week of the current calendar year.

What I'd like to do though is be able to type ON TOP of an existing date and have it add to THAT date. I can't find the syntax for this. I've tried "++" and "=+" but these don't work.

It seems odd that the options above are covered but adding to the date in the cell isn't covered.

thanks in advance

Ed

CycleBagEd

Hi@Jeff Reisman, thanks but it isn't formulae I'm looking for - it's features with which my users can interact with their project management tools.

I'll leave a suggestion for Smartsheet, because it would enhance the existing functionality to be able to add to a date in situ, especially in conjunction with a "ctrl+enter" function for bulk entering, like excel. That way you could add, e.g. 9 weeks to all of the start dates in your project without having to export to excel to achieve it (assuming dependencies aren't set).

Jeff Reisman

Hi folks.<\/p>

So, today I discovered that if you are in a date column and type "+1" then it creates a date 1 day from today, and if you type "+15w" it creates a date 15 weeks from now.<\/p>

If you just type "16w" you get the 16th week of the current calendar year.<\/p>

What I'd like to do though is be able to type ON TOP of an existing date and have it add to THAT date. I can't find the syntax for this. I've tried "++" and "=+" but these don't work.<\/p>

It seems odd that the options above are covered but adding to the date in the cell isn't covered.<\/p>

thanks in advance<\/p>

Ed<\/p>","bodyRaw":"[{\"insert\":\"Hi folks.\\nSo, today I discovered that if you are in a date column and type \\\"+1\\\" then it creates a date 1 day from today, and if you type \\\"+15w\\\" it creates a date 15 weeks from now.\\nIf you just type \\\"16w\\\" you get the 16th week of the current calendar year.\\nWhat I'd like to do though is be able to type ON TOP of an existing date and have it add to THAT date. I can't find the syntax for this. I've tried \\\"++\\\" and \\\"=+\\\" but these don't work.\\nIt seems odd that the options above are covered but adding to the date in the cell isn't covered.\\nthanks in advance\\nEd\\n\"}]","format":"rich","dateInserted":"2022-05-20T15:53:57+00:00","insertUser":{"userID":142740,"name":"CycleBagEd","url":"https:\/\/community.smartsheet.com\/profile\/CycleBagEd","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-12T12:18:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/91353\/adding-to-dates-without-a-formula","embedType":"quote","name":"Adding to dates without a formula"}"> https://community.smartsheet.com/discussion/91353/adding-to-dates-without-a-formula

That's a pretty cool discovery.

However, you can only add days to an existing date value, unless you take the individual date components and do math on those.

For instance, =StartDate@row + 7 will add 7 days to the StartDate.

If you just wanted to add 2 months to the date, you can either approximate it by using =StartDate@row + 60, or you can break the StartDate into it's pieces and rebuild it:

=DATE(YEAR(StartDate@row), MONTH(StartDate@row) + 2, DAY(StartDate@row))

So if your StartDate is 3/14/22, the formula above would return 5/14/22.

CycleBagEd

Hi@Jeff Reisman, thanks but it isn't formulae I'm looking for - it's features with which my users can interact with their project management tools.

I'll leave a suggestion for Smartsheet, because it would enhance the existing functionality to be able to add to a date in situ, especially in conjunction with a "ctrl+enter" function for bulk entering, like excel. That way you could add, e.g. 9 weeks to all of the start dates in your project without having to export to excel to achieve it (assuming dependencies aren't set).