What formula need to use to eg: between 11/03/2024 16:00:00 and 12/03/2024 09:00 to return as next day is mean 12/03/2024. currently i use formula =IF($A2="","",IF($A2="","No Scan",INT($A2)+IF(MOD($A2,1)>=TIME(16,0,0),1,0))). Attached the file for reference. Hope someone can help to solve this matter.
to use between mm/dd/yyyy and mm/dd/yyyy to return as next day
- Thread starter rafiz87
- Start date
p45cal
Well-Known Member
I'm not at a computer right now but perhaps
INT(A2+(1/3))
or
INT(CEILING.MATH(A2,1/3))
1/3 of a day being 8 hours, 16:00 + 8 hours = midnight.
I will look more closely later.
Edit: later: seems OK, with a difference with what happens at exactly 16:00 where the CEILING formula aligns with your original formula results.
INT(A2+(1/3))
or
INT(CEILING.MATH(A2,1/3))
1/3 of a day being 8 hours, 16:00 + 8 hours = midnight.
I will look more closely later.
Edit: later: seems OK, with a difference with what happens at exactly 16:00 where the CEILING formula aligns with your original formula results.
Last edited: