Formula to Calculate Hours Worked in Excel & DAX: Day or Night Shift! Excel Magic Trick 1826

Learn how to take time values and calculate hours worked for day or night shift with Excel formulas or DAX Formulas. Lean about the famous MOD function to make this calculation easy. Learn how the MOD function algorithm works.
Topics:
1. (00:00) Introduction
2. (00:28) Time as proportion of 24 hours.
3. (01:20) Calculate hours work when there is no nightshift.
4. (01:48) Formula #1: Logical Formula. Calculate hours work when there are some who work past midnight, or the nightshift.
5. (03:32) Formula #2: MOD Function. Calculate hours work when there are some who work past midnight, or the nightshift.
6. (04:28) MOD function algorithm explained.
7. (06:42) Closing, Video Links

#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp

#nightshift #mod #module #time #payroll

[wizardofaus]

Outstanding as always Mike. You are the number =MOD(3, 2) teacher of Excel. Thank you

[Siggy]

Great tip. I always used date and time as input, makes end minus start always work and you can see on what day the hours where done

[simoiyahector-morales]

Never a dull moment with you Teacher.😊

[ianl]

Where were you 6 years ago when I was spending hours trying to calculate day shift and night shift hours finally coming up with a massive formula?
The GOD (sorry, MOD) function really is a gamechanger.

[sujeetsamal]

Logic of all possible solutions are interesting for time.... This is saviour in HR work OT, parking area charge, hourly wage payment etc....

[roywilson]

Oh boy dealing with time arithmatic in Excel was enough to bring me out in a cold sweat when I first had to deal with durations and workdays and networkdays. A lot of the methods needed seemed so counter-intuitive to me. Thank heaven we can now create a fewLambda functions and need only check the results are good once. That said, another great video with sensible examples.

[Excelambda]

Great video !!
Other solution is to always use NOW() when you register an event. Shifts in hospitals or military can take more than 1 day.
You will say, yes but NOW() recalculates. No problem, we can make is stop .
The simplest lambda in history 😉
NW(cl) cl: cell's reference where we want to register the event, the very cell where we call the function.
=LAMBDA(cl, IF(INDIRECT(cl)=0, NOW(), INDIRECT(cl)))
- call: =NW("c8")
If you do not edit the cell anymore it will not recalculate.
Used this concept before when I needed random array samples to "freeze". Can be done with any function that recalculates.
Note: Since is a circular reference we need this checked: iterative calculation

[richardhay]

Yes, INT rounding/truncating is the key to it's simplicity and to efficient explanation of how it works. Excel uses that approach beyond MOD. For example: SEQUENCE.

[kebincui]

magic use of mod for this case, mind blowing; thanks Mike

[erikguzik]

OH my - a Math lesson.... Ha ha ha - Great job Mike, you always explain stuff so well. Saving this video and linking it to ever person that asks, "how to i figure out a persons work hours". Biggest problem is, seems like no one understands what TIME in excel really is (could say the same for Date too, but thats another vid)👍👍

[GeertDelmulle]

This is a great video about a great topic, Mike!
This is the first time I see someone giving us a simple solution to the base problem (er, “challenge”, er, I mean: opportunity ;-) and explain it as exquisitely as you. This is one major Golden Nugget, right here!
Thanks a lot! :-)

[richardhay]

Whether I expect situations that require it or not, I always use the MOD wrap with [h]:mm format to create a bulletproof formula/format combo. Thus ready for anything.

[barttitulaerexcelbart]

Hey Mike, great explanation of the MOD. As a friend I feel free to say that this solution might be a bit to difficult to understand for beginners. I would first explain that 24.00 = 1 (time that we go from day 1 to day 2) and then give the cells with start and end a name: then use this formula: IF=(END>START; END-START; 1-START (part on day 1) + END (part on day 2) ) . Your teammate Bart.

[nadermounir]

I finally understood it today 🙌 Thank you Mike for sharing your great knowledge with us 😀

[RahulSharma-noys]

Hello sir,
I got to now about your channel today only. I have never done excel in my life and want to learn it from basics. Can you tell me the order in which i have to follow playlists available on your channel? Can you give me the link of playlist from where i can start. Thank you so much

[mishalqamar]

Please share your opinion on power bi Microsoft certification

[TopBam]

Mike, outstanding video as always! I use time calculations all the time. I've been using my own really long formulas to achieve the same result (yours are definitely best)! On another note, I downloaded your spreadsheet, and I noticed that you changed your lambda formula to show formulas. I've been using the one you had on your comprehensive lambda video, and it would sometimes give me blank rows, even though TOCOL has argument to ignore blank rows. Then I looked at your new formula, and I noticed that it has the "isformula" function wrapped in an "or" function by itself, so I tried to use it without the "or" function and quickly realized the reason it was wrapped, which doesn't make any sense at all (maybe you can explain that in a future video). I came up with a formula that I think will always work and it doesn't have weird formula combinations:
=LAMBDA(Reference, LET( r, Reference, TOCOL(IF(ISFORMULA(r), ADDRESS(ROW(r), COLUMN(r), 4) & ": "& FORMULATEXT(r), #VALUE!), 3, 1)))

[davelaverie]

The MOD operator in VBA also produces results differing from the Excel MOD formula. It doesn't work for night shift.

[Reduce_Scan]

For first formula

=(C8-B8)*24

If we use this formula we get what is required

=(C8-B8)*24+24*(C8<B8)

[alirezahossini]

I have example of and very complex, can I send to you and tech us how you solve that, and I love power query 😊