Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spread Sheet Employee Hours Issue
Here's the Excel time sheet I've been working on, and here's the problem:
This is an example of an employee who works the overnight shift: When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total hrs do not calculate properly. If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is not an acceptable solution since I want to turn this worksheet over to the managers to enter employee's times, and they'll not 'get' it. Just FYI, this is for an assisted living home that has 24 hr staffing. They have been using military time, and it confuses everyone when it comes to totaling the hours, so we want Excel to do the calculating. Do you think you can fix this mess for us?? You'll be my hero! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spread Sheet Employee Hours Issue
Let's say A1 =21:00, B1 =09:00
C1 =(B1-A1)+(A1B1) "Kelsey" wrote: Here's the Excel time sheet I've been working on, and here's the problem: This is an example of an employee who works the overnight shift: When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total hrs do not calculate properly. If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is not an acceptable solution since I want to turn this worksheet over to the managers to enter employee's times, and they'll not 'get' it. Just FYI, this is for an assisted living home that has 24 hr staffing. They have been using military time, and it confuses everyone when it comes to totaling the hours, so we want Excel to do the calculating. Do you think you can fix this mess for us?? You'll be my hero! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spread Sheet Employee Hours Issue
=IF(E11E10, E11-E10,(E11+24)-E10)
Where E11 is the finish time and E10 is the start time. "Kelsey" wrote: Here's the Excel time sheet I've been working on, and here's the problem: This is an example of an employee who works the overnight shift: When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total hrs do not calculate properly. If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is not an acceptable solution since I want to turn this worksheet over to the managers to enter employee's times, and they'll not 'get' it. Just FYI, this is for an assisted living home that has 24 hr staffing. They have been using military time, and it confuses everyone when it comes to totaling the hours, so we want Excel to do the calculating. Do you think you can fix this mess for us?? You'll be my hero! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spread Sheet Employee Hours Issue
I click ENTER key too soon from my previous reply...
make sure format cell as hh:mm "Kelsey" wrote: Here's the Excel time sheet I've been working on, and here's the problem: This is an example of an employee who works the overnight shift: When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total hrs do not calculate properly. If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is not an acceptable solution since I want to turn this worksheet over to the managers to enter employee's times, and they'll not 'get' it. Just FYI, this is for an assisted living home that has 24 hr staffing. They have been using military time, and it confuses everyone when it comes to totaling the hours, so we want Excel to do the calculating. Do you think you can fix this mess for us?? You'll be my hero! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spread Sheet Employee Hours Issue
I wouldn't recommend adding 24 days, Cin. (It will give an answer of 564
hours instead of 12). 1 day would make more sense. -- David Biddulph "Cin" wrote in message ... =IF(E11E10, E11-E10,(E11+24)-E10) Where E11 is the finish time and E10 is the start time. "Kelsey" wrote: Here's the Excel time sheet I've been working on, and here's the problem: This is an example of an employee who works the overnight shift: When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total hrs do not calculate properly. If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is not an acceptable solution since I want to turn this worksheet over to the managers to enter employee's times, and they'll not 'get' it. Just FYI, this is for an assisted living home that has 24 hr staffing. They have been using military time, and it confuses everyone when it comes to totaling the hours, so we want Excel to do the calculating. Do you think you can fix this mess for us?? You'll be my hero! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spread Sheet Employee Hours Issue
If you have it formated as 24h clock it all works with a +24, sorry - forgot
to mention that bit! ;-) "David Biddulph" wrote: I wouldn't recommend adding 24 days, Cin. (It will give an answer of 564 hours instead of 12). 1 day would make more sense. -- David Biddulph "Cin" wrote in message ... =IF(E11E10, E11-E10,(E11+24)-E10) Where E11 is the finish time and E10 is the start time. "Kelsey" wrote: Here's the Excel time sheet I've been working on, and here's the problem: This is an example of an employee who works the overnight shift: When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total hrs do not calculate properly. If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is not an acceptable solution since I want to turn this worksheet over to the managers to enter employee's times, and they'll not 'get' it. Just FYI, this is for an assisted living home that has 24 hr staffing. They have been using military time, and it confuses everyone when it comes to totaling the hours, so we want Excel to do the calculating. Do you think you can fix this mess for us?? You'll be my hero! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Spread Sheet Employee Hours Issue
Hours that cross midnight can be calculated by:
=E11-E10+(E10E11) or =MOD(E11-E12,1) What I should have written of course was: Any hours including hours that cross midnight....... -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Sandy Mann" wrote in message ... What David was saying is valid. "Cin" wrote in message ... =IF(E11E10, E11-E10,(E11+24)-E10) Where E11 is the finish time and E10 is the start time. With 01:00 in E11 and 23:00 in E10 =IF(E11E10, E11-E10,(E11+24)-E10) returns 23.0833333, formtted as hh:mm it does indeed only *show* as 2:00 but it nevertheless still has an extra 23 days included in the value actually held in the cell. If you want to multiply the elepsed time by a pay rate the normal way of doing so is to multiply the *time* value by 24 to convert it to decimal hours and then multiply the decimal hours by the pay rate. ie if the result of your formula is in cell F12 and the pay rate is 10 then =F12*24*10 formatted as currency returns 5,540.00 dollars or pounds for a two hour shift - one hell of a pay for a two hour shift! Hours that cross midnight can be calculated by: =E11-E10+(E10E11) or =MOD(E11-E12,1) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Cin" wrote in message ... If you have it formated as 24h clock it all works with a +24, sorry - forgot to mention that bit! ;-) "David Biddulph" wrote: I wouldn't recommend adding 24 days, Cin. (It will give an answer of 564 hours instead of 12). 1 day would make more sense. -- David Biddulph "Cin" wrote in message ... =IF(E11E10, E11-E10,(E11+24)-E10) Where E11 is the finish time and E10 is the start time. "Kelsey" wrote: Here's the Excel time sheet I've been working on, and here's the problem: This is an example of an employee who works the overnight shift: When we enter 21:00 as an 'In' time, and 09:00 as the 'Out' time, the total hrs do not calculate properly. If I convert 09:00 to 33:00 (24:00 + 09:00) it works properly, but this is not an acceptable solution since I want to turn this worksheet over to the managers to enter employee's times, and they'll not 'get' it. Just FYI, this is for an assisted living home that has 24 hr staffing. They have been using military time, and it confuses everyone when it comes to totaling the hours, so we want Excel to do the calculating. Do you think you can fix this mess for us?? You'll be my hero! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
macro | Excel Discussion (Misc queries) | |||
Returning Values | Excel Discussion (Misc queries) | |||
Totals sheet - deal with employee names | Excel Worksheet Functions | |||
Referencing a newly created worksheet | Excel Worksheet Functions |