Can i get get help with a date formula please?

Chrisr

Hi team, what is the formula to auto populate a date based on another date? ie "date 1" equals 7 days after "order date"?

Jason P

Hi Chris

Your looking for Workday function which excludes Sat and Sun as it suggests. First, make sure all 4 column properties are set as a Date type. In [date 1] cell insert the formula =WORKDAY(order date@row, 7) This simply counts 7 working days from the date in your [Order Date] column. In date 2 & 3 cells on the same row insert the same formula changing the 7 to 14 and so on for date 3.

If there is no date in [order date] date 1, 2 & 3 will pop up with an error message #Invalid date type. You can avoid this using the =IFERROR function

=IFERROR(IF(ISDATE([order date]@row), WORKDAY([order date]@row, 7), ""), "")

To make these a column formula to capture all new entries, right click the cells with the result and at base of menu select Convert to column formula.

Hope this helps.

KPH

Make sure both order date and date 1 are "Date" format columns.

Use the following formula in date 1

=[order date]@row + 7