How do I calculate the difference in hours from now and the next workday's 9am?
If NOW() is Thursday 6pm and the next workday is Friday, the function would then return 15.
If NOW() is Friday 5pm and the next workday is Monday, the result is then 64.
What's the formula to do that? For simplicity's sake, I don't need to take into account holidays.
1 Answer
The number of hours will be simply the next business day at 9:00 AM minus the starting time (formatted as [hh]:mm to allow the display of >24 hour periods). The trick will be getting the number representing the next business day at 9:00 AM correct.
The formula in B2 is,
=WORKDAY(A2,1*(HOUR(A2)>9))+TIME(9,0,0)
This makes the formula in C2,
=(WORKDAY(A2,1*(HOUR(A2)>9))+TIME(9,0,0))-A2
Excel treats a boolean FALSE as zero when used mathematically. A TRUE is a one. By checking if the time of the start date/time is less than the current day, an additional day is not added until after 9:00 AM.
5
