Forum Discussion
CALCULATING 24 HOUR TIME TO THE NEAREST QUARTER HOUR
I bill clients and pay contractors to the nearest quarter hour. I cannot seem to put together a formula that will do the job.
say start time is 0800 hrs stop time 1215 hrs. the formula (B-A)/100 returns 4.15 and not the 4.25 needed to calculate pay at the appropriate rate
but if start time is 0730 hrs and stop time is 1715 the formula (B-A)/100 returns 9.85 and not 9.75 needed
is there a step function i can incorporate? or do I have to create a table and use a look up function? perhaps something simple I haven't thought of?
3 Replies
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.
- You are close. The answer would be =(B-A)*24. The B-A returns 4:15, but if you format it as a decimal (not time) it becomes 0.17708.... depending on how many digits you display. In Excel going from 0 to 1 in "time" is the same from going from midnight one day, to midnight the next day. What you need to do is convert from 0...1 to 0...24. Multiplying by 24 does that.
- Your question is not clear. Can you show a screenshot of your data then show what output do you want? Why you are dividing by 100 to find a quarter?
