Formula for past dates
Hi All,
What's formula can I use to workout past dates, based on below example.
Thanks in advance,
Best Answers
-
If Due-Date and the formula columns are date formatted columns and Project Completion Date is always on row 7, then this formula should do what you need. It will take the date in the Due-Date column on row 7 and subtract the value in the duration column on the current row.
=[Due-Date]$7 - Duration@row
Here is is pasted into rows 3-6 in the Formula column:
If Project Completion is not always on row 7, we can use a different formula to find the row it is on.
-
You can use the WORKDAY function.
=WORKDAY([Due-Date]$7, -Duration@row)
That will remove Saturdays and Sundays, and you can specify holidays, by including the holiday date like this:
=WORKDAY([Due-Date]$7, -Duration@row, DATE(2024, 2, 29))
Or referencing a column with holidays in, like this:
=WORKDAY([Due-Date]$7, -Duration@row, Holidays:Holidays)
Answers
-
If Due-Date and the formula columns are date formatted columns and Project Completion Date is always on row 7, then this formula should do what you need. It will take the date in the Due-Date column on row 7 and subtract the value in the duration column on the current row.
=[Due-Date]$7 - Duration@row
Here is is pasted into rows 3-6 in the Formula column:
If Project Completion is not always on row 7, we can use a different formula to find the row it is on.
-
Hi KPH,
That's exactly I was looking for - Thank you.
Is it possible to exclude weekends (Sat/Sun) and holidays.
-
You can use the WORKDAY function.
=WORKDAY([Due-Date]$7, -Duration@row)
That will remove Saturdays and Sundays, and you can specify holidays, by including the holiday date like this:
=WORKDAY([Due-Date]$7, -Duration@row, DATE(2024, 2, 29))
Or referencing a column with holidays in, like this:
=WORKDAY([Due-Date]$7, -Duration@row, Holidays:Holidays)
-
Worked like a charm KPH. Many thanks 😀
-
Great news!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!