Date Formula
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
Answers
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!