Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time)

See how to calculate hours worked for a night shift where workers start at 9 PM and end at 5 AM. See how to use the MOD function and learn the algorithm that MOD uses to calculate. See how to deal with negative Time.

This is an important Time Math / Payroll / Time Sheet Excel solution. It solves the Night Shift time problems in Excel Time Formulas.

Category:

Science & Technology

Tags:

• Excel
• MOD
• function
• Time
• Calculations
• hours
• worked
• for
• night
• shift
• Negative
• more
• than
• 24
• excelisfun
• Mike
• Gel
• Highline
• Community
• College

[falcoperegrinus82]

Thanks! This was driving me crazy until I found this video.

[ExcelIsFun]

You are welcome!

[mesq007]

Dear Mike,

May I suggest another solution. Just add one "day" if the second time is earlier than the first one.

=SE(C13>C12;C13-C12;1+C13-C12)

Thanks

[ExcelIsFun]

Nice!

[cumnkisme]

Dear Mike,

Dates and times in Excel must be positive values. ... Had this trouble though the formula is correct, although the result is a negative. Thank you so much for this great tutorial it helps a lot. Even how much i research in internet no one explain it better than you! You're the BEST!!! Thank you so much!!!

--Rhea Bruna

[ExcelIsFun]

I am glad that the video helps!!

[harrybkidd]

Hi - used your formula and it work easily, BUT it give Hours and a percentage of hours (i.e. 1.25) not hours and minutes (1:15) is there away to have the result express in hour:minutes?

[aron925r]

ITS A GREAT VIDEO I LEARNT A LOT!! JUST ONE QUESTION, IM TRYING TO USE THE CTRL+SHIFT+~ FOR THE SHORTCUT AND IT DSNT ALLOW ME TO DO IT. DO U HAVE ANY SUGGESTIONS? THANKS AGAIN FOR THE VIDEO VERY HELPFUL! :)

[ysadstore]

@dirtylittlecritter yeah. exactly is =mod(1st time - 2nd time,1)

[ExcelIsFun]

You are welcome!