How do I convert 30m duration to a format that can be used in a timevalue calculation?
I have two columns for times for start and end of a period, with a separate column with the equivalent Timevalue for each to enable me to subtract the start time from the end time. However I also have a column with a duration representing a break, and this is expressed as eg 30m. How do I subtract the break time from the calculation result for the Timevalue calculation?
I am currently using the formula below:
IFERROR(MOD(F12−B12,"1d")−D12,"")
But the problem here is it does not work when comparing across midnight. for example,
Start time = 21:40 (9.40pm) ==> Timevalue 0.4028
Break = 30m
End time = 09:50 (the next day) ==> Timevalue 0.4097
The answer I am expecting is 12h 10m - 30m = 11h 40m but what I get is -0h 20m.
Where am I going wrong?
Mac Studio, macOS 14.6