Problem with time calc

[Tom]

I have a spreadsheet for my timesheet. I can get a correct display of
hours and minutes by using the following format:

[h]:mm

Each pay period is comprised of two weeks. So I add the daily totals
to get weekly totals. And then I add the two weekly totals to get
period totals. So far, so good.

Now I want to show the regular hours for the period up to 80 hours. I
have another box for overtime hours. My problem is that I cannot get
Excel (2000) to display what I want.

For the regular hours box I'm using:

=IF(K42>80, 80, K42)

For the overtime box I'm using:

=IF(K42>80, (K42-80), 0)

My totals are okay. For week 1 I have 40 hours, and week 2 I have
41:30. Giving me a period total of 81:30. My weekly totals are good,
and my period totals are good. BUT...

my regular hours cell shows 81:30

and my overtime cell shows 0:00.

Anyone know what I'm missing?

Thanks in advance,

Tom

 

[Biff]

Hi!

If cell K42 is formatted as [h]:mm and displays 81:30, the
problem is that your using the decimal value of 80 in your
formulas to compare against the FORMATTED TIME of 81:30.
The decimal value of 80:00 hours would be 80/24 = 3.333333
and is <80 so your formulas return the FALSE argument.

Reg hours formatted as [h]:mm

=IF(K42>80/24,80/24,K42)

OT hours formatted as [h]:mm

=IF(K42>80/24,K42-(80/24),0)

Biff

 

[Tom]

That was it. Works great. Thanks so much!

Hi!

If cell K42 is formatted as [h]:mm and displays 81:30, the
problem is that your using the decimal value of 80 in your
formulas to compare against the FORMATTED TIME of 81:30.
The decimal value of 80:00 hours would be 80/24 = 3.333333
and is <80 so your formulas return the FALSE argument.

Reg hours formatted as [h]:mm

=IF(K42>80/24,80/24,K42)

OT hours formatted as [h]:mm

=IF(K42>80/24,K42-(80/24),0)

Biff

-----Original Message-----
I have a spreadsheet for my timesheet. I can get a correct display of
hours and minutes by using the following format:

[h]:mm

Each pay period is comprised of two weeks. So I add the daily totals
to get weekly totals. And then I add the two weekly totals to get
period totals. So far, so good.

Now I want to show the regular hours for the period up to 80 hours. I
have another box for overtime hours. My problem is that I cannot get
Excel (2000) to display what I want.

For the regular hours box I'm using:

=IF(K42>80, 80, K42)

For the overtime box I'm using:

=IF(K42>80, (K42-80), 0)

My totals are okay. For week 1 I have 40 hours, and week 2 I have
41:30. Giving me a period total of 81:30. My weekly totals are good,
and my period totals are good. BUT...

my regular hours cell shows 81:30

and my overtime cell shows 0:00.

Anyone know what I'm missing?

Thanks in advance,

Tom



.