Formula for past dates

✭✭✭✭
edited 03/13/24

Hi All,

What's formula can I use to workout past dates, based on below example.

Tags:

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!