Hourly time calculation

[Kheckent]

I am trying to write a formula that will calculate the difference
between time in and time out, that will figure to the 1/4 hour and if a
time is 7 minutes before the 1/4 hour it registers as that 1/4 hour.
ie: 7:06am is 7:00am and 7:07am is 7:15am, 3:36pm is 3:30pm and 3:37pm
is 3:45pm and the total hours worked for 7:06am to 3:36pm is 8.25. can
this be done?

 

[Peo Sjoblom]

=ROUND(A2/(1/96),0)*1/96

your example is wrong however, round to the nearest 15th minute will result
in 07:07 being 07:00, not
07:15. If the time is greater than or equal to 07:07:30 then it will round
to 07:15
So you have gotten rounding wrong if you think 7 minutes will round to 15

You might want to do the calculation first

=(ROUND(totaltime/(1/96),0)*1/96)*24

to get decimal time

--

Regards,

Peo Sjoblom

I am trying to write a formula that will calculate the difference
between time in and time out, that will figure to the 1/4 hour and if a
time is 7 minutes before the 1/4 hour it registers as that 1/4 hour.
ie: 7:06am is 7:00am and 7:07am is 7:15am, 3:36pm is 3:30pm and 3:37pm
is 3:45pm and the total hours worked for 7:06am to 3:36pm is 8.25. can
this be done?


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Paul]

I am trying to write a formula that will calculate the difference
between time in and time out, that will figure to the 1/4 hour and if a
time is 7 minutes before the 1/4 hour it registers as that 1/4 hour.
ie: 7:06am is 7:00am and 7:07am is 7:15am, 3:36pm is 3:30pm and 3:37pm
is 3:45pm and the total hours worked for 7:06am to 3:36pm is 8.25. can
this be done?

If you really want 7:07 to round up to 7:15, you will need to add 1 minute
( TIME(0,1,0) ) before rounding.
Also, are you rounding start and finish times separately before subtracting?
I will assume so.
But then 7:06 rounds to 7:00 and 3:36pm rounds to 3:30pm, which gives 8.5
hours, not 8.25. I shall assume you meant to start at 7:07 (rounding to
7:15) and finish at 3:36pm (rounding to 3:30pm) to get 8.25 hours.

With start time in A1 and finish time in A2:
=ROUND((A2+TIME(0,1,0))*96,0)/4-ROUND((A1+TIME(0,1,0))*96,0)/4