Help

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

Solved
Jump to Solution
1793 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Candice_Mooney
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

13 Replies 13

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

Candice_Mooney
5 - Automation Enthusiast
5 - Automation Enthusiast

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: