If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate hours worked minus a half hour for lunch
I'm trying to find the formula to calculate the employee hours worked in a
given day allowing for a half hour lunch. The time card entry looks like: Time in: 6:00 Time out: 14:30 I want the answer to be 8 |
#2
|
|||
|
|||
Calculate hours worked minus a half hour for lunch
Hi
Excel stores times as fractions of a day, so 1 hour is 1/24, hence half hour is 1/48 With Start time in A1 and End Time in B1 =B1-A1-1/48 For the cells with the formula, FormatCellsNumberCustomhh:mm If you need to Sum the hours derived by the formula, format that cell as [h]:mm to allow it to accumulate past 24 hours. If your start and end times are likely to span over 2 days, then use =MOD(B1-A1,1)-1/48 -- Regards Roger Govier "Danedel" wrote in message ... I'm trying to find the formula to calculate the employee hours worked in a given day allowing for a half hour lunch. The time card entry looks like: Time in: 6:00 Time out: 14:30 I want the answer to be 8 |
#3
|
|||
|
|||
Calculate hours worked minus a half hour for lunch
And, if you want the formula to be clearer that it's 30 minutes, use:
=b1-a1-time(0,30,0) Regards, Fred. "Roger Govier" roger@technology4unospamdotcodotuk wrote in message ... Hi Excel stores times as fractions of a day, so 1 hour is 1/24, hence half hour is 1/48 With Start time in A1 and End Time in B1 =B1-A1-1/48 For the cells with the formula, FormatCellsNumberCustomhh:mm If you need to Sum the hours derived by the formula, format that cell as [h]:mm to allow it to accumulate past 24 hours. If your start and end times are likely to span over 2 days, then use =MOD(B1-A1,1)-1/48 -- Regards Roger Govier "Danedel" wrote in message ... I'm trying to find the formula to calculate the employee hours worked in a given day allowing for a half hour lunch. The time card entry looks like: Time in: 6:00 Time out: 14:30 I want the answer to be 8 |
#4
|
|||
|
|||
Calculate hours worked minus a half hour for lunch
Or just
=B1-A1-"00:30" -- Regards, Peo Sjoblom "Fred Smith" wrote in message ... And, if you want the formula to be clearer that it's 30 minutes, use: =b1-a1-time(0,30,0) Regards, Fred. "Roger Govier" roger@technology4unospamdotcodotuk wrote in message ... Hi Excel stores times as fractions of a day, so 1 hour is 1/24, hence half hour is 1/48 With Start time in A1 and End Time in B1 =B1-A1-1/48 For the cells with the formula, FormatCellsNumberCustomhh:mm If you need to Sum the hours derived by the formula, format that cell as [h]:mm to allow it to accumulate past 24 hours. If your start and end times are likely to span over 2 days, then use =MOD(B1-A1,1)-1/48 -- Regards Roger Govier "Danedel" wrote in message ... I'm trying to find the formula to calculate the employee hours worked in a given day allowing for a half hour lunch. The time card entry looks like: Time in: 6:00 Time out: 14:30 I want the answer to be 8 |
Thread Tools | |
Display Modes | |
|
|