Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 06, 2022 08:53 AM
Hello, I am new here. I am also new to formulas.
Have managed to work out the formula to calculate the number of 12hr shifts between two date/time fields.
ROUNDUP(((DATETIME_DIFF({DepartureDate},{ArrivalDate},‘minutes’) /60 ) /12), 0)
My struggle is that shifts are from 06:00 to 18:00 day shift and 18:00 to 06:00 night shift. Therefore if a start time is any time between 06:00 and 18:00 I need it to 'snapback to the default start 06:00. And the same for if the start time is any time between 18:00 and 06:00, I need it to ‘snapback’ to the default start 18:00. Bearing in mind that these are date and time fields formatted YYYY-MM-DD HH:mm.
Can anybody help with the correct formula? I hope my explanation makes sense!
Solved! Go to Solution.
Nov 08, 2022 09:58 PM
Sigh! Thank you for your patience, I’ve updated the base again to handle that
Nov 07, 2022 01:06 AM
Hi Candice, any chance you could provide some example data and also indicate the desired number of 12 hour shifts for said example data?
It’d make trying to help you with this a lot easier!
Nov 07, 2022 03:35 AM
Hi Adam
So basically the app allows employee to log their arrival date and time and departure date and time from a site. The client is not billed per hour but rather per 12 hour shift. The shifts are from 06:00-18:00 (day shift) and 18:00-06:00 (night shift). I therefore need to calculate the DATETIME _DIFF to be calculated from the beginning of the first shift to the end of the last shift.
So I can workout the datetime difference and convert to hours and then to 12hour shfts even round up to the nearest shift but first need to make the first shift calculate from the start of that particular day or night shift and the last shift end at the end of that particular day or night shift .
This is what I have in my table so far. Its complicated and I cannot get my head around it and am beginning to think it cannot be done?
Would appreciate any assistance to move me forward.
Nov 07, 2022 04:54 AM
Hi Candice, thanks for the details!
I take it the highlighted cell should show “6”?
If so, I’ve put together a base here for you that should do what you’re looking for
To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button
Nov 07, 2022 05:23 AM
Thank you, and I wish that solved the problem.
The Arrival and Departure date fields are input by the team member who is assigned to the specific job. They could be called out at any time and would have to log their arrival / departure and therefore I would need ‘Start of Shift’ field to find the beginning of the shift in ‘ArrivalDate’ automatically as the ArrivalDate is sent to the database. ie if the start time is between 06:00 and 18:00 it should default to 06:00 and if its between 18:00 and 06:00 it should default to 18:00.
Nov 07, 2022 05:42 AM
Apologies, I didn’t see the formula in the new field. That has done the trick. Thanks a million!
Nov 07, 2022 06:48 AM
So I’ve copied the cells and the formulas but the results are not the same as yours?
What am I doing wrong!?
Nov 07, 2022 07:04 AM
Hmm! Could you show the time zones for all of the date and formula fields please?
This might be happening due to one of the fields operating on a different timezone
Nov 07, 2022 07:53 AM
Ok, yes, so I’ve checked and formatted all timezones to be the same and all seems to be fine now with the current data.
I also used your conditions formula for the ‘DepartureDate’ field and just swapped the ‘IS_BEFORE’ TO ‘IS_AFTER’ to make sure I get the end of the last shift. Like this:
IF(
AND(
IS_AFTER(
DepartureDate,
DATETIME_PARSE(
DATETIME_FORMAT(
DepartureDate,
“DD MM YYYY”
) & " 06",
“DD MM YYYY HH”
)
),
IS_BEFORE(
DepartureDate,
DATETIME_PARSE(
DATETIME_FORMAT(
DepartureDate,
“DD MM YYYY”
) & " 18",
“DD MM YYYY HH”
)
)
),
DATETIME_PARSE(
DATETIME_FORMAT(
DepartureDate,
“DD MM YYYY”
) & " 18",
“DD MM YYYY HH”
),
DATETIME_PARSE(
DATETIME_FORMAT(
DepartureDate,
“DD MM YYYY”
) & " 06",
“DD MM YYYY HH”
)
)
I think thats right?
This all seems to work fine if the shift is day shift, 06:00-18:00, on the same day, but what happens when there is a night shift which runs from 18:00 to 06:00 the following day? See the first field starting at 02:30, the Start of shift defaults to 18:00 which would be correct if it was the previous day.
Thanks so much for your help.
Nov 07, 2022 07:50 PM
Ah, yes, so sorry
I’ve updated the formula in the base, does this look right?