Hi, I would like to calculate the halfway point between two date-time entries, but then I want as a result a time occurring between 8:00 and 20:00. So if the halfway point is outside this timeframe and before 2:00, it would round down to 20:00 the day before, after 2:00, it would round up to 8:00. How would I do this?
Thanks in advance for your help!
What about case "after 20:00" - should it round down to 20:00 current day ?
Yes, sorry, anything after 20:00 on same day would also round to 20:00
Hello,
while waiting for a response to my comment, see the formula in column I of following sample file:
HalftimeByConditions.ods
Formula in column I reads (formatted here for better readability of the 4 conditions used)
=IFS( MOD(($B2+$C2)/2;1)<2/24 ;INT(($B2+$C2)/2)-4/24;
AND(MOD(($B2+$C2)/2;1)>2/24;MOD(($B2+$C2)/2;1)<8/24);INT(($B2+$C2)/2)+8/24;
MOD(($B2+$C2)/2;1)>20/24 ;INT(($B2+$C2)/2)+20/24;
1 ;($B2+$C2)/2)
Note(s)
D through H are not required for the formula to work (only column B and C are used). I left em for the sake of understanding, how the formula works and for breakdown/testing purposes.20:00 to 20:00 (round down).J
Hope that helps.
Awesome! This is exactly what I wanted to calculate. Thank you so much!