Excel Magic Trick 718: Calculate Hours Worked (Day or Night Shift) & Subtract Lunch

We have In Time & Out Time for either Day or Night Shift and we must subtract 1 hour for weekday lunch and 1/2 hour for weekend lunch. See the formula to make this calculation that uses the IF, MOD and WEEKDAY functions. In case the In Time and Out Time are blank or have the word "OFF", we will use the ISNUMBER and SUMPRODUCT to show a zero.
Download file: http://people.highline.edu/mgirvin/

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• Time
• Math
• Calculate
• Hours
• Worked
• Day
• Night
• Shift
• Subtract
• Lunch
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Slaying
• Dragons
• IF
• MOD
• WEEKDAY
• functions
• ISNUMBER
• SUMPRODUCT
• function
• formula
• Payroll

[HotPapaRyan]

Thank you so much! Really appreciate this man!

[ExcelIsFun]

You are welcome!

[zandieice012]

wait..it's hard because i have excel on my macbook and never used it.. how would i calculate the time from ...lets say 3:45 - 7pm ? is that like 4 hours and 15 mins? so like...4.2? if i want to add all the hours together? :[

[ExcelIsFun]

I am sorry. i do not know how to use a Mac (zero).

Try this site:

mrexcel..com/forum

[Alekz22136]

thanks for sharing!!!!

[ExcelIsFun]

You are welcome!

[KHOLOUD911]

Hi MOD formula works great if there is no minutes to be calculated in the time cells but this formula doesn't work if I need to do e.g ( 3:30 AM - 9:45 PM = 6h :15M ) so is there any solution if I need to calculate the number of hours and minutes as well ??

and Thank you ur videos are really great

[josephcabaguio]

what formula are you gonna put if there is no break..if its less than 6 hours and there is no break..how are you gonna put it in?

[AndyChiu3]

why cant you just do A1(Start) = 5:00 am A2(end time) = 6:00 pm A3(Break) = 0 A4( Hours worked) = (A2-A1)-A3

XD