Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem with time calc
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(K4280, 80, K42) For the overtime box I'm using: =IF(K4280, (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 |
#2
|
|||
|
|||
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(K4280/24,80/24,K42) OT hours formatted as [h]:mm =IF(K4280/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(K4280, 80, K42) For the overtime box I'm using: =IF(K4280, (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 . |
#3
|
|||
|
|||
That was it. Works great. Thanks so much!
On Fri, 5 Nov 2004 23:31:42 -0800, "Biff" wrote: 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(K4280/24,80/24,K42) OT hours formatted as [h]:mm =IF(K4280/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(K4280, 80, K42) For the overtime box I'm using: =IF(K4280, (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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
Elapsed time Calc...over 30 days long... | Excel Discussion (Misc queries) | |||
Elapsed time Calc...over 30 days long... | Excel Discussion (Misc queries) | |||
Elapsed time Calc...over 30 days long... | Excel Discussion (Misc queries) |