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 
Ads 
#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 =B1A11/48 For the cells with the formula, Format>Cells>Number>Custom>hh: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(B1A1,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:
=b1a1time(0,30,0) Regards, Fred. "Roger Govier" <[email protected]> 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 > =B1A11/48 > For the cells with the formula, Format>Cells>Number>Custom>hh: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(B1A1,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
=B1A1"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: > > =b1a1time(0,30,0) > > Regards, > Fred. > > "Roger Govier" <[email protected]> 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 >> =B1A11/48 >> For the cells with the formula, Format>Cells>Number>Custom>hh: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(B1A1,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  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
how to calculate hours worked daily & minus 30 min lunch, 7am5pm  Debby_Jo  Excel Discussion (Misc queries)  1  July 23rd 08 03:40 AM 
How can I calculate daily hours worked minus lunch to eual a decim  lili  Excel Worksheet Functions  6  March 28th 07 03:28 AM 
how do I round hours worked to the next half hour with a 24hr shif  gycoso3  Excel Worksheet Functions  1  March 28th 06 09:25 PM 
Calculate time difference to the half hour  Ken Ivins  Excel Worksheet Functions  6  July 17th 05 05:48 PM 
How do I multiply rate per hour by hours worked.  MAC  Excel Worksheet Functions  1  December 22nd 04 03:29 PM 