Formula for past dates

Saj

Hi All,

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

Thanks in advance,

KPH

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

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)

KPH

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

Hi KPH,

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

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

KPH

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

Worked like a charm KPH. Many thanks 😀

KPH

Great news!