Date Formula

CJ Dijkstra

Good Day. I am setting up a service schedule for equipment and would like some advise on a formula.

Sheet 1 - Calendar setup which indicates "Workdays"

S1-C1; S1-C2; S1-C3; S1-C4

Sheet 2:

S2-C1; S2-C2; S2-C3; S2-C4; S2-C5; S2-C6; S2-C7; S2-C8; S2-C9

S2-C7 DATE = Previous service date

S2-C3 Workdays = S2-C1 / S2-C2

S2-C4 DATE = I need a formula to add the value of C3 to that of C7 based on Sheet 1 Workdays

ALSO

S2-C9 DATE = (Workdays: S2-C8 - S2-C5) / S2-C2) + TODAY() based on Sheet 1 Workdays

Thanks

CJ

Genevieve P.

Hi @CJ Dijkstra

I had to re-create your sheet and name the columns something a little more recognizable to identify what it is you're looking to do - you may need to adjust the names of the columns in my formulas below to match your own.

S2-C4 DATE

For your first one, S2-C4 DATE, before we can work on this formula you'll need to add a ROUND function to your S2-C3 Workdays = S2-C1 / S2-C2

=ROUND(S2-C1 / S2-C2, 0)

This way it will product a whole number that we can use in your S2-C4 DATE column.

Then, we can use a WORKDAY function to add the number of days in your S2-C3 Workdays column to the date in the S2-C7 column, excluding the dates found in your other sheet that have a 0, noting it as a non-working day:

=WORKDAY(Date, Number, Holidays)

=WORKDAY([S2-C7]@row, [S2-C3]@row, COLLECT({S1-C1}, {S1-C4}, 0))

The COLLECT function first lists the range we want to look through (the Dates on the first sheet), then the range with criteria (the 1s and 0s), and the criteria (0).

S2-C9 DATE

Then we can do something similar for this other column. First we need to find the ROUNDED number of days from (S2-C8 - S2-C5) / S2-C2

=ROUND(([S2-C8]@row - [S2-C5]@row) / [S2-C2]@row, 0)

Then this is the number we'll use in the WORKDAY function:

=WORKDAY(Date, Number, Holidays)

so:

=WORKDAY(TODAY(), ROUND(([S2-C8]@row - [S2-C5]@row) / [S2-C2]@row, 0), COLLECT({S1-C1}, {S1-C4}, 0))

Let me know if this works for you!

Cheers,

Genevieve