Formula for past dates

Saj
Saj ✭✭✭
edited 03/13/24 in Formulas and Functions

Hi All,

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

Thanks in advance,

Tags:

Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    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.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    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

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    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.

  • Saj
    Saj ✭✭✭

    Hi KPH,

    That's exactly I was looking for - Thank you.

    Is it possible to exclude weekends (Sat/Sun) and holidays.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    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)

  • Saj
    Saj ✭✭✭

    Worked like a charm KPH. Many thanks 😀

  • KPH
    KPH ✭✭✭✭✭✭

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!