Round time with conditions

[Guest]

Hello,
I am in an odd predicament. I am trying to figure out how to calculate time
on my timesheet with certain conditions. Our time is calculated by thirds of
an hour, but not the same way that Excel calculates it. Here is how our time
is calculated:
1-10 min. after an hour= no time
11-30 min after an hour = 1/3 of an hour
31-50 min after an hour = 2/3 of an hour
51-59 min after an hour = 1 full hour
but Excel calculates it as:
1-9 min after an hour = no time
10-29 min after an hour = 1/3 of an hour
30-50 min after an hour = 2/3 of an hour
51-59 min after an hour = 1 full hour
I am currently using the formula =ROUND(D18*72,0)/72*24 where D18 displays
the actual time calculated like form 8:30 am to 9:00 am equals :30, and the
cell the formula is in will disply the time as it is to be submitted(eg..333)
but again when the time is calculated out, the number is slightly off.
Anyones help on this matter would be greatly greatly greatly appreciated.
Thanks, Kim

 

[Steve]

Try

=ROUND(B1*71.99999,0)/72*24



Steve

 

[Guest]

Hi Kim,
I got confused, so if this does not help, oh well. Starts A1 with "In"
calculates minutes, then uses an If statement, which is in D2.

In Out Minutes PdMinutes
8:01 AM 9:00 AM 59.00 60.00
C2=(+D18-C18)*24*60
D2=IF(E18<11,0,IF(E18<31,0.33,IF(E18<51,0.66,IF(E18<=59,60,"?"))))
thanks

 

[Ron Rosenfeld]

Hello,
I am in an odd predicament. I am trying to figure out how to calculate time
on my timesheet with certain conditions. Our time is calculated by thirds of
an hour, but not the same way that Excel calculates it. Here is how our time
is calculated:
1-10 min. after an hour= no time
11-30 min after an hour = 1/3 of an hour
31-50 min after an hour = 2/3 of an hour
51-59 min after an hour = 1 full hour
but Excel calculates it as:
1-9 min after an hour = no time
10-29 min after an hour = 1/3 of an hour
30-50 min after an hour = 2/3 of an hour
51-59 min after an hour = 1 full hour
I am currently using the formula =ROUND(D18*72,0)/72*24 where D18 displays
the actual time calculated like form 8:30 am to 9:00 am equals :30, and the
cell the formula is in will disply the time as it is to be submitted(eg..333)
but again when the time is calculated out, the number is slightly off.
Anyones help on this matter would be greatly greatly greatly appreciated.
Thanks, Kim

Just subtract one minute from the time you are rounding

=ROUND((D18-TIME(0,1,0))/TIME(0,20,0),0)*TIME(0,20,0)

or, if you want the result in decimal hours:

=ROUND((D18-1/1440)*72,0)/3


--ron

 

[Guest]

Worked perfect! Thanks so much.

Try

=ROUND(B1*71.99999,0)/72*24



Steve