How to Calculate Hours Worked with Excel Power Query (& Properly Sum time)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/calculate-time-file

[@TTTU.Nanami]

You are my savior... Literally... Love and respect

[@wayneedmondson1065]

Hi Leila, thanks for the excellent demo and lesson on manipulating time with Power Query. Your thorough and complete explanations are always appreciated. Thumbs up!

[@LeilaGharani]

Glad you enjoyed it, Wayne!

[@vijayarjunwadkar]

Alas! If only I would have known this on "Time", when I needed it two years before! I struggled a lot that time with formulas, formatting and macros to somehow solve it! So once again Leila, thanks for showing a simple yet amazing way of handling time data! You are Super! :-)

[@LeilaGharani]

I've been there too :) Hope it will come in handy in the future.

[@everclearacg]

Hey thanks for this tutorial. Using this video as a guide let me figure production per hour per day for each person on my team. Total game changer, I’m excited to apply this to other reports.

[@MrJrhzues]

youre still the best!!! no one can beat you girl!!!

[@gabriellaprievara7379]

Thank you Leila, excellent as always. I do not know how, but your videos come always at the best time! :)

[@LeilaGharani]

That's great to hear Gabriella :)

[@phpvbacoder9000]

Very useful Video. Im just learning Power Query and creating good looking Dashboards in Excel. Your videos have really taken me further here.

[@AgesOfAgony]

Mam, you're a blessing to the Excel community! 👌✨

[@cambike]

Been working on this for so long, this is soo much easier than all the other methods. THANK YOU x

[@LeilaGharani]

You're so welcome!

[@nonoobott8602]

Wow.....this is so amazing. I don't have to worry about formulas to calculate datetimes. PQ is a game changer. Thanks for sharing Leila

[@LeilaGharani]

It really is :)

[@wannabedal-adx458]

I wish i had known about this 8-9 years ago!!! Excellent video, Leila!!

[@kevinbolt999]

Excellent. I’m going to investigate using this with time in of a query to time out of a query, taking into account working hours, weekends, and holidays

[@rachidmoutawakk1834]

Thanks Leila for the work. wish you all the best

[@StaticBlaster]

Love your thumbnail. The Back to the future movies are flippin awesome. By the way thanks for these videos. Very invaluable source of information regarding spreadsheets.

[@ahs4494]

Always useful tutorials. Thank you.

[@felipelandim2881]

Find it very useful. Nice to have a spreadsheet to practice. Took me about 20min to do everything as in the video (translating the buttons to portuguese in my head hehe). Thank you!

[@LeilaGharani]

Excellent!

[@azeezahmed7070]

Essential topic ,very nicely explained and simplified. Thanks a lot for sharing.

[@davidbecker4669]

OMG you are the Queen ! Thanks !

[@StaticBlaster]

Yes! Yes she is. The queen of knowledge!

[@irkMukut]

How can someone speak so beautifully!!!!!

[@wlouiz]

Thanks Lelia. Very helpful as always

[@LearnExcelVBA]

Thanks Leila for sharing this. This is surely going to help lot of employers and freelancers who regularly track time in excel.

[@LeilaGharani]

I hope it'll be helpful.

[@shashwatmishra3023]

Hi , this one is really good.... I admire your teaching skill's a lot . Keep doing the Good work..

[@LeilaGharani]

Thank you, I will

[@shadrackawunyo7786]

Very nice presentation Leila. I love this

[@mariostudio7]

Wow so it remembers that we've merged the two columns we deleted for Work Day and repeats the process in the background! This is amazing 😍

[@LeilaGharani]

Yes, exactly! PQ is great!

[@ericaleverson9430]

Thank you so much! I did not know it was this easy! I've been trying to do DAX formulas that were not working right.

[@waltermlk]

Goodness...You really are good at this...Thanks

[@gmccormack1234]

Great collection! Thank you for the videos.

[@oladapoomolaja4104]

Thanks Leila, this is very insightful

[@IvanCortinas_ES]

Excellent work and explanation Leila! I would appreciate more Power Query videos because they help a lot with data processing. Thank you very much!!

[@mattschoular8844]

Thanks Leila.... I can see these techniques being very helpful in many business situations. Great explanation.

[@LeilaGharani]

I hope it will come in handy, Matt.

[@krn14242]

Happy Thursday Leila. Thanks...

[@LeilaGharani]

Same to you! Thank you :)

[@OrcunBahadir]

Thank you very much Leila for this wonderful tutorial. I have been trying to find a way to properly calculate the duration in Power BI and this helped me a lot...

[@praavi5444]

THANK YOU SO MUCH FOR THIS TUTORIAL!!!!!!!!!!!!!! YOU SAVED ME

[@LeilaGharani]

Glad it helped!

[@sachinrv1]

I straight away hit LIKE. Lovely video and Lovely Leila :)

[@abdallah.kandiel]

This is a useful one Happy Thursday your highness 🙏

[@LeilaGharani]

Glad to hear it! Happy Thursday to you too :)

[@fcoatis]

Thank you. Very nice explanation.

[@junnelduluguin5324]

Hi, Leila, can you create a delivery or shipping schedules with time cut-off per location or destination?

[@wofdigy]

Another wonderful video Leila.. thank you..

[@LeilaGharani]

Thank you too!

[@ibrahim82649]

Hello Leila you are a life saver 😘😘😘.

[@LeilaGharani]

Happy to help!

[@syamkumar-tc3xo]

Superb information about date and time data

[@MultiSciGeek]

Great thanks! Saving me for the second time today XD

[@SUPERmuzhroom]

thanks for the video, very helpful, i was wondering how to do the basic time, undertime and overtime in this video

[@Luciano_mp]

I like the power query, good, Thank you Leila!

[@hazemali382]

very great video ♥ many thanks

[@musicaenlife]

Hey Leila you are awesome. Thanks for sharing great stuff..

[@LeilaGharani]

Thanks for watching!

[@aimendondo]

Great session! Thank you very much.

[@LeilaGharani]

Glad you enjoyed it!

[@Зле_Коте]

Thank you! It`s awesome tutorial

[@chrism9037]

Great job Leila!

[@LeilaGharani]

Thanks Chris 😊

[@sherwinrickyjonas214]

Hi Leila, I need to calculate the time in minutes between two time values considering the Company working time between 7am to 2.30 pm and break time between 12 noon to 12.30 pm

[@serawitbogale3144]

Thanks , I was searching for this all over for a long time. Thank you. Please also we need the transformation of time to power BI

[@darrylmorgan]

Hi Leila!I Have Set This Same Scenario Up For Project Managers In Work Using Power Query...Thank You For A Really Enjoyable Tutorial :)

[@LeilaGharani]

My pleasure Darryl!

[@alm6885]

Yes! Excellent lesson.

[@aman9028206170]

Informative, Thanks Leila 🙂

[@Sunriseto]

Hello, great video. How do I add another column to calculate the hours work compared to previous day for different employees. Apparently, the first day work hours variance from previous day for the employee will be zero. I can’t simply use the next row work hours subtract the previous one as there will be different employee after certain row. Let me know if you can help! Thank you

[@gintomino4136]

I love powerquery!

[@fjeniferjeni]

Miss Leila, please provide guidance on which project will take how many hours per week.

[@apolloistaphquibs5075]

Hi Leila, do you have a link for google sheets on this? Thanks.

[@karl2776]

This was so, so helpful

[@LeilaGharani]

Happy to hear that!

[@alwarhi]

Many thanks 🥰

[@fraziqbal6938]

wonderful. thank you very much!

[@bellomdragon1838]

hello, je suis heureux de vous comprendre et de vous écouté helas y a plus de traduction automatique sur certaine video... MERCI A VOUS POUR VOS SUPER TUTO

[@RicardoSadaJapp]

Great tip! Thanks

[@tonytaf107]

Any suggestions to covert for example: 1m 30s to 1.5 or 4m 30s to 4.5 ???? Please

[@rubencorderotorres2915]

Thanks Leila, I'd like to know if power query could calculate working days, considering holy days and Saturday and sundays

[@dhrumildarji683]

Can you please tell me how to calculate Time in hh:mm:ss format when Distance and speed is available

[@ElectronicBarta]

awesome video. thanks for sharing.

[@Deependra1991]

Previously i was subtracting end to start time and then converting to minutes n second using text fn and pasting special to values or multiplying hours to 1440 to get numbers in minutes. Well its way to easy in power query as compared to earlier way of calculations.

[@Ashley-ry4we]

HI Leila I Think This Video Is a little advanced for Me is there another video that kind of slows things down a little, I'm not familiar with all these functions but this is exactly what I need to learn. I just need a beginners course on this. Do you have one?

[@hilaryjansz4687]

Great work

[@Theportraitdude]

Extremely helpful video as always! Thank you so much for what you do and your style of teaching. I have learned so much and referred many of my friends and coworkers to your video over the years :)

[@LeilaGharani]

Great to hear! Thanks for your support.

[@mohamadhadid2403]

you are the best

[@rahulbhujbal3663]

Beauty with Brain, rare combination

[@ubaidyaseencubaidyaseenc8207]

Amazing ❤

[@HusseinKorish]

Perfect ... Thanks Leila

[@Timmy_1011]

You're the best ever

[@vytran8047]

Hi dear, how about the noon 1-hour noon?

[@aicoandaya1920]

How can I make power query recognize the time and date properly?? really appreciate if you respond to my question. thank you

[@imransadiq5744]

Hey, if time is in the format of 17:05 and 01:05. It's 24 hours format and if someone worked between two days. The substraction gives a negative number in answer which is wrong. In Excel the fix for this problem is MOD function, would you please share a fix for this in Power Query. Thanks in Advance

[@taizoondean689]

Thanks 🙏🙏

[@vida1719]

It's a great idea to use Power Query in time calculations. You can also get an age by simply using User Interface

[@LeilaGharani]

👍

[@Jcook941]

Would you have any videos or ideas for tracking employee training. E.g courses completed. But allowing for employees and courses to be added in the future?

[@jaimecalderon1432]

Thank you Leila

[@blindshark4560]

Thanks for this Leila

[@brankahrehor6677]

Hi Leila, can I calculate the time difference, for example, if I want to know if delivery was on time, in case it was late or arrived earlier than expected, it means I need to calculate the time both back and forth?

[@tharikum4289]

How to link the encrypted excel sheet using power query?

[@alexrobatz1513]

Oh Today is on Thursday.... 😜😜 I love your work Mom

[@LeilaGharani]

😊 thank you

[@foysalahmed5587]

It's Awesome Tricks, Thanks for sharing.

[@knutboehnert3163]

Nice use and despite the lazy workers (rounding up to impress their manager) will make me use PowerQuery a lot more. It is just hard as a Non-American to read these weird time formats. As a database engineer I just wish more people use (and present) ISO 8601 standards (which coincidentally makes imports from Excel into a database just so much smoother)

[@IamHiteshRathod]

This is great 👌 Thanks 😊

[@LeilaGharani]

You're welcome 😊

[@ramkyadi]

Wow so simple

[@stevejobs5533]

What about calculating overtime? Ive noticed in the video the hours arent differentiated i.e hours worked on saturday or nightwork are rated the same as hours worked during weekdays during the day. Is power query able to differentiate that?

[@tharindusilva6909]

Hi Eeila I need a help! How to calculate the total hours if end date & time is less than the start date & time?

[@WJA5]

I think I can use this to calculate length of service without using a formula...what are your thoughts? Thank you!

[@pedrohfcostapc]

Hi Leila, thx for all your videos, makes complicated things a lot easy. I would like if you already have a video for this problem: I want to make an inventory for 100 products. I would like to make a FIFO for them but having PURCHASE TABLE (date bought, name, qtity, price), USAGE TABLE (date used, name, qtity, automated price from fifo inventory table) and INVENTORY TABLE (with one row for each product showing name, balance, price, total.) What would be the best approach for this? Tnx for your time.

[@mohamedabdellatif86]

Very Wonderful 🤩

[@rasikagujarathi5780]

Can you show how it is to be done with vba

[@shahidahammed478]

Please help !!! Using pivot i made summary of sales country wise and divisions in country grouped under country head. I want to put static target number for countries and divisions as well but how can i do it?

[@GrugeSilva]

Excelente vídeo. Parabéns!

[@Jhun_L_Roxas]

Thank you!! This is very powerful especially for project based work or for manufacturing. Question on this however... how do I factor in lunch, dinner and midnight break times of let us say 1 hour each?