Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then statement and time format
Trying to calculate Overtime using IF THEN. All cells are formatted for time.
If E22 is greater than 40 hours, I want 40, else E22. E22 is 51:00:00 and the statement keeps comming back with the full 51:00:00. Excel 2007. Thanks in advance, I know it is something simple but after 45 minutes, I'm fed up Don |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then statement and time format
It would help if you posted the formula you're using, but I'll guess that
you're comparing to the number 40. If you've got everything in time format, you probably want to compare to 40/24 instead (40 would be 40 days; 40/24 makes it 40 hours). "DonElston" wrote: Trying to calculate Overtime using IF THEN. All cells are formatted for time. If E22 is greater than 40 hours, I want 40, else E22. E22 is 51:00:00 and the statement keeps comming back with the full 51:00:00. Excel 2007. Thanks in advance, I know it is something simple but after 45 minutes, I'm fed up Don |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then statement and time format
Try:
=IF(E22*2440,40,E22*24) Formatted as NUMBER "DonElston" wrote: Trying to calculate Overtime using IF THEN. All cells are formatted for time. If E22 is greater than 40 hours, I want 40, else E22. E22 is 51:00:00 and the statement keeps comming back with the full 51:00:00. Excel 2007. Thanks in advance, I know it is something simple but after 45 minutes, I'm fed up Don |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If then statement and time format
Thanks
I still got bizzzre results, BUT rather than using the E22 result, I redid the cell sums and with the 40/24, got the correct result. =IF(SUM(H5:H11)40/24,SUM(H5:H11)-40/24,0) Figures OT =IF(SUM(H6:H12)40/24,40/24,SUM(H6:H12)) Regular Thanks again "Toppers" wrote: Try: =IF(E22*2440,40,E22*24) Formatted as NUMBER "DonElston" wrote: Trying to calculate Overtime using IF THEN. All cells are formatted for time. If E22 is greater than 40 hours, I want 40, else E22. E22 is 51:00:00 and the statement keeps comming back with the full 51:00:00. Excel 2007. Thanks in advance, I know it is something simple but after 45 minutes, I'm fed up Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if then statement = time | Excel Worksheet Functions | |||
If statement with time | Excel Discussion (Misc queries) | |||
IF statement to calculate time usage in specific time bands | Excel Worksheet Functions | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |