Round time with conditions
 

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

Round time with conditions
 

Try

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



Steve


"KimLL" wrote in message
...
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

Round time with conditions
 

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(E1 8<=59,60,"?"))))
thanks

"KimLL" wrote:

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

Round time with conditions
 

On Mon, 14 Feb 2005 13:25:06 -0800, "KimLL"
wrote:

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

Round time with conditions
 

Worked perfect! Thanks so much.

"Steve" wrote:

Try

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



Steve


"KimLL" wrote in message
...
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