3 Power Query Formulas: Hours Worked for Day & Night Shift from Time Values. Excel Magic Trick 1827

Download Excel File: excelisfun.net/files/EMT1826-...
Learn about how to calculate hours worked from time values in Power Query. Learn about the importance of M Code Values and Data Types when creating formulas. See three different formulas.
Topics:
1. (00:00) Introduction.
2. (00:21) How Power Query Formulas are different than in the Worksheet and DAX formulas.
3. (01:55) Hours Worked From Time Values for Day Shift using Duration.TotalHours function.
4. (03:13) Hours Worked From Time Values for Night Shift using let expression, the Number.From Function and Number.RoundDown function.
5. (06:45) Hours Worked From Time Values for Night Shift using the Number.From Function and a Logical Test.
6. (08:13) Summary of Formulas used in Video
7. (08:33) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #powerquery #powerquerytutorial #microsoftexcel #microsoftmvp #powerquery #mcode #time #payroll #accounting #excelacademy #math #mathematics #mcoder

[@excelisfun]

P.S. If you have datetime values, the day and night shift formulas are the same: Duration.TotalHours([End]-[Start]). Why? Because the End datetime number is bigger than the Start datetime value. But often, we don't get our times this way. P.S. I am working on a new M Code Power Query book that will have this tip too. Out next year : )

[@GeertDelmulle]

That’s exactly what I wanted to comment (as you know), then again that’s putting extra responsibility on the input.
I do think the datetime approach is the ‘cleaner’ one.

[@excelisfun]

@@GeertDelmulle Yah, but most people do not do it that way : (

[@ExcelInstructor]

97k to go!
and then "The tablet" the 1 mln subs to best channel on youtube!

[@ExcelInstructor]

yeah I also found out that dealing with dates and time is way simpler then in excel :)
GReat video!

[@excelisfun]

@@ExcelInstructor Thank for your kind words, my friend : ) : )

[@domsaneria7439]

This helped me out massively, was wading through several forums and posts before coming to this. I did have an issue though that took me a while to resove I had to multiply the results by 60 to get it back to where I needed it. thanks for your help

[@excelisfun]

Glad it helped!!

[@chrism9037]

PQ rules!!! Thanks Mike!

[@excelisfun]

I agree, Chris M!!!! : )

[@snakeeyesOFFICIAL76]

You are the best!

[@excelisfun]

Thanks! Just trying to have fun : )

[@SyedMuzammilMahasanShahi]

Thanks Mike for this EXCELlent video.

[@excelisfun]

You are welcome, Most Awesome Fellow Teacher!!!

[@mattschoular8844]

Thanks Mike. Now get out there and ride..... Happy BMXing....

[@excelisfun]

You are welcome, Matt!!! Thanks for the happy BMX wishes. I did have fun with my son today at two different skateparks : )

[@nadermounir8228]

Great video Mike 📹 👍 Thank u for ur hard work 👏

[@excelisfun]

You are welcome, Teammate!!!

[@richardhay645]

PQ book? Great idea!! Is this the FIRST EVER comprehensive PQ book? I think so?!?!

[@excelisfun]

It is not a Power Query book. It is an M Code book. It assues that you already use the user interface to make queries. But... the book will be M Fun and M Rad ; )

[@johnborg5419]

Thanks Mike!! :) Very Interesting, the PQ solutions. :)

[@excelisfun]

Glad you like it, Formula Guy!!!!!

[@jawadahmadehssan6251]

Thank you Mike, very useful.

[@excelisfun]

You are welcome!!!!!

[@alializadeh8195]

thanks

[@excelisfun]

You are welcome!!!!

[@jerrydellasala7643]

Great stuff as always! Quick question. I'm on the Beta Insider. A few weeks ago PQ now adds "Table_" to the beginning of a query name when it's loaded as a Table, and then within the last week a query created From Table Range now ads "Query_" to the beginning of the Table's name. I hate it, ESPECIALLY since I name tables carefully to make them easier to type into formulas. What do you think of these new "features"?

[@excelisfun]

Yes, I hate it too because I always name my objects carefully. I think they did it because most people do change names and then you have two objects with same name. But sadly, it does not seem to help with the recursion error with the Excel.CurrentWorkbook function...

[@ManiKumar-nf9fr]

I had a excel query but not relating to this topic, can you plz resolve?
when we applied a formula in a cell with cell references for example, lets take A1*B1 where A1=5 and B1=2
Now , formula bar shows
=A1*B1 but how i can make the formula bar to actually show the values containing in those cell references like formula bar should show =5*2
Is there any shortcut to extract the values of cell references, so that formula bar shows the calculation with actual values of those cell references?

[@josh_excel]

Nice

[@excelisfun]

Thanks, Josh Excel!!! I just subbed to your channel : )

[@adnanakram2217]

No one like small you tubers😢😢

[@excelisfun]

We love all Excel UA-camrs : ) : )

[@excelisfun]

I just subbed to your channel!

[@adnanakram2217]

Thanks 🙏 alot

[@richardhay645]

Nam of yout channel?