Help

Substitute HH:mm in DateTime field with new HH:mm based on conditions

Topic Labels: Formulas
Solved
Jump to Solution
111 13
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

Sigh! Thank you for your patience, I’ve updated the base again to handle that

Screenshot 2022-11-09 at 1.58.52 PM

See Solution in Thread

13 Replies 13

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!

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?

Screenshot 2022-11-07 at 13.28.35
Screenshot 2022-11-07 at 13.29.25

Would appreciate any assistance to move me forward.

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

Screenshot 2022-11-07 at 8.53.54 PM

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

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.

Apologies, I didn’t see the formula in the new field. That has done the trick. Thanks a million!

So I’ve copied the cells and the formulas but the results are not the same as yours?
What am I doing wrong!?
Screenshot 2022-11-07 at 16.40.09

Hmm! Could you show the time zones for all of the date and formula fields please?

Screenshot 2022-11-07 at 11.03.23 PM

This might be happening due to one of the fields operating on a different timezone

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.

Screenshot 2022-11-07 at 17.48.08

Thanks so much for your help.

Ah, yes, so sorry

I’ve updated the formula in the base, does this look right?

Screenshot 2022-11-08 at 11.50.01 AM

Yes, that’s great! Thanks so much for taking the time to help with this!

Another challenge…
Just testing the table and the input time 06:00 defaults to 18:00 previous day. Any idea how to fix this?

Screenshot 2022-11-08 at 21.17.00

Sigh! Thank you for your patience, I’ve updated the base again to handle that

Screenshot 2022-11-09 at 1.58.52 PM

Thats amazing! Thank you Adam. I am very grateful for people like you :grinning_face_with_big_eyes:

Labels