| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating Time
Hello. I want to calculate the time a production unit is in a position. The
operator should only have to enter "time in" and "time out". The formula should automatically deduct non-work hours. For example from 6:00 to 8:00 would be 2 hours (very simple). But from 11:00 to 14:00 would be 2 hours also because there is a lunch from 12:00 to 13:00. From 16:00 to 7:00 should be 2 hours also because shift ends at 17:00 and begins again at 6:00. I can't find an example to use for this. Please help if you can! -- Thanks, Mike |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Time
Mike,
With the "Time In" in Cell D3 and the "Time Out" in E3 does: =IF(COUNT(D3:E3)<2,"",IF(AND(D3<0.5,E30.5),E3-D3-1/24,IF(E3<D3,(17/24-D3)+(E3-6/24),E3-D3))) do what you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Mike Archer" wrote in message ... Hello. I want to calculate the time a production unit is in a position. The operator should only have to enter "time in" and "time out". The formula should automatically deduct non-work hours. For example from 6:00 to 8:00 would be 2 hours (very simple). But from 11:00 to 14:00 would be 2 hours also because there is a lunch from 12:00 to 13:00. From 16:00 to 7:00 should be 2 hours also because shift ends at 17:00 and begins again at 6:00. I can't find an example to use for this. Please help if you can! -- Thanks, Mike |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Time
Sandy - That is very helpful. I made a mistake though. I thought that if
someone provided me a formula, I would be able to figure out how it works, therefore, I gave hypothetical times to keep the example simple. I am not able to decypher your formula. Would you mind helping modify it for the real times? The shift is 6:00 to 16:30, and lunch is 11:00 to 11:30. -- Thanks, Mike "Sandy Mann" wrote: Mike, With the "Time In" in Cell D3 and the "Time Out" in E3 does: =IF(COUNT(D3:E3)<2,"",IF(AND(D3<0.5,E30.5),E3-D3-1/24,IF(E3<D3,(17/24-D3)+(E3-6/24),E3-D3))) do what you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Mike Archer" wrote in message ... Hello. I want to calculate the time a production unit is in a position. The operator should only have to enter "time in" and "time out". The formula should automatically deduct non-work hours. For example from 6:00 to 8:00 would be 2 hours (very simple). But from 11:00 to 14:00 would be 2 hours also because there is a lunch from 12:00 to 13:00. From 16:00 to 7:00 should be 2 hours also because shift ends at 17:00 and begins again at 6:00. I can't find an example to use for this. Please help if you can! -- Thanks, Mike |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Time
Thank you so much!
-- Thanks, Mike "Sandy Mann" wrote: Try: =IF(COUNT(D3:E3)<2,"",IF(AND(D3<=11/24,E3=11.5/24),E3-D3-0.5/24,IF(E3<D3,(16.5/24-D3)+(E3-6/24),E3-D3))) Times as fractions of a day so 1 hour is 1/24th of a day ie 1/24 so 11:30 AM is 11.5/24 etc -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Mike Archer" wrote in message ... Sandy - That is very helpful. I made a mistake though. I thought that if someone provided me a formula, I would be able to figure out how it works, therefore, I gave hypothetical times to keep the example simple. I am not able to decypher your formula. Would you mind helping modify it for the real times? The shift is 6:00 to 16:30, and lunch is 11:00 to 11:30. -- Thanks, Mike "Sandy Mann" wrote: Mike, With the "Time In" in Cell D3 and the "Time Out" in E3 does: =IF(COUNT(D3:E3)<2,"",IF(AND(D3<0.5,E30.5),E3-D3-1/24,IF(E3<D3,(17/24-D3)+(E3-6/24),E3-D3))) do what you want? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Mike Archer" wrote in message ... Hello. I want to calculate the time a production unit is in a position. The operator should only have to enter "time in" and "time out". The formula should automatically deduct non-work hours. For example from 6:00 to 8:00 would be 2 hours (very simple). But from 11:00 to 14:00 would be 2 hours also because there is a lunch from 12:00 to 13:00. From 16:00 to 7:00 should be 2 hours also because shift ends at 17:00 and begins again at 6:00. I can't find an example to use for this. Please help if you can! -- Thanks, Mike |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculating Time
You're very welcome. My wife's got me scraping wallpaper so it was a
welcome breal <g -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Mike Archer" wrote in message ... Thank you so much! -- Thanks, Mike "Sandy Mann" wrote: Try: =IF(COUNT(D3:E3)<2,"",IF(AND(D3<=11/24,E3=11.5/24),E3-D3-0.5/24,IF(E3<D3,(16.5/24-D3)+(E3-6/24),E3-D3))) Times as fractions of a day so 1 hour is 1/24th of a day ie 1/24 so 11:30 AM is 11.5/24 etc -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating time without using time functions... | Excel Worksheet Functions | |||
| Calculating time and negative time | Excel Worksheet Functions | |||
| calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
| Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
| Calculating effective time from start/end date+time | Excel Worksheet Functions | |||
Linear Mode
