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

[Candice_Mooney]

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!

[TheTimeSavingCo]

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!

[Candice_Mooney]

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.

[TheTimeSavingCo]

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

[Candice_Mooney]

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.

[Candice_Mooney]

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

[Candice_Mooney]

So I’ve copied the cells and the formulas but the results are not the same as yours?
What am I doing wrong!?

[TheTimeSavingCo]

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

[Candice_Mooney]

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.

[TheTimeSavingCo]

Candice_Mooney:

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.

Ah, yes, so sorry

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