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!

[Candice_Mooney]

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

[Candice_Mooney]

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

[TheTimeSavingCo · Accepted solution]

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

[Candice_Mooney]

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