Forum Discussion
CALCULATING 24 HOUR TIME TO THE NEAREST QUARTER HOUR
Please be aware that 0730 and 1715 are just numbers for Excel. They are not seen as time values. So, you deduct 730 from 1715 and you end up with 985. Divide by 100 and get 9.85. But this is not a time value.
Enter as 7:30 and 17:15, deduct and you get 9:45 (i.e. 9 hours, 45 minutes) being a time value of 0.40625. Multiply this by 24 to arrive at hours in decimals of 9.75.
However, your question was about charging to the nearest whole quarter. Let's assume you use a time tracker that records time by the minute. The start is at 7:35 and the end at 17:27. That results in 9 hours and 52 minutes or 9.866667 hours (in decimal). Now use the MROUND function to round to the nearest multiple of 0.25 (hours) which will be 9.75. Add one more minute and the nearest quarter will be 10.00.
See also the attached file.