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

[@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

[@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!

[@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! 👌✨

[@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

[@MrJrhzues]

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

[@ahs4494]

Always useful tutorials. Thank you.

[@davidbecker4669]

OMG you are the Queen ! Thanks !

[@StaticBlaster]

Yes! Yes she is. The queen of knowledge!

[@fjeniferjeni]

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

[@rachidmoutawakk1834]

Thanks Leila for the work. wish you all the best

[@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.

[@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!

[@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?

[@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.

[@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

[@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.

[@SUPERmuzhroom]

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

[@apolloistaphquibs5075]

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

[@praavi5444]

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

[@LeilaGharani]

Glad it helped!

[@wlouiz]

Thanks Lelia. Very helpful as always

[@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.

[@irkMukut]

How can someone speak so beautifully!!!!!

[@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 :)

[@eslamfahmy87]

Thanks alot, but I facing an issue with some of rows calculation in my file like the below Exp ( start time 11: 58 Pm) and (end time 12: 04 Am) and after I followed ur instructions it's calculated 1457 minutes instead 4 minutes. Appreciate your support 🙏

[@waltermlk]

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

[@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 :)

[@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.

[@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!

[@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?

[@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.

[@shadrackawunyo7786]

Very nice presentation Leila. I love this

[@fcoatis]

Thank you. Very nice explanation.

[@syamkumar-tc3xo]

Superb information about date and time data

[@oladapoomolaja4104]

Thanks Leila, this is very insightful

[@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

[@rubencorderotorres2915]

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

[@krn14242]

Happy Thursday Leila. Thanks...

[@LeilaGharani]

Same to you! Thank you :)

[@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.

[@WJA5]

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

[@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

[@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...

[@alm6885]

Yes! Excellent lesson.

[@aimendondo]

Great session! Thank you very much.

[@LeilaGharani]

Glad you enjoyed it!

[@aicoandaya1920]

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

[@chrism9037]

Great job Leila!

[@LeilaGharani]

Thanks Chris 😊

[@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.

[@wannabedal-adx458]

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

[@gmccormack1234]

Great collection! Thank you for the videos.

[@zdzislawkes]

Hi Very good training material. Thank you. Only one question: In this case, the formula = (C5-B5) * 24 works well. In what case calculate Hours Worked especially when they go past midnight can get tricky in Excel? Regards

[@hazemali382]

very great video ♥ many thanks

[@MultiSciGeek]

Great thanks! Saving me for the second time today XD

[@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?

[@Зле_Коте]

Thank you! It`s awesome tutorial

[@RicardoSadaJapp]

Great tip! Thanks

[@karl2776]

This was so, so helpful

[@LeilaGharani]

Happy to hear that!

[@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?

[@ElectronicBarta]

awesome video. thanks for sharing.

[@wofdigy]

Another wonderful video Leila.. thank you..

[@LeilaGharani]

Thank you too!

[@tonytaf107]

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

[@LifeofSheru]

Hi Leila - Would be kind enough to advise how can I speed up calculation on an excel workbook which is big and has loads of different formulas?

[@moazzamca1]

Hi Leila, that's an awesome solution from you as usual, great job! I have learned a lot from your videos, wondering if you can demonstrate how we can prepare a FIFO based stock aging calculation model using Power Query. I can provide a sample I have developed using formulas and am sure it can be simplified through Power Query. Thanks

[@abdallah.kandiel]

This is a useful one Happy Thursday your highness 🙏

[@LeilaGharani]

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

[@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?

[@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.

[@bryanc8275]

Great video. Could apply this to tracking case work if you could find the duration between 2 dates/time but exclude weekends and non working hours

[@dhrumildarji683]

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

[@gintomino4136]

I love powerquery!

[@ibrahim82649]

Hello Leila you are a life saver 😘😘😘.

[@LeilaGharani]

Happy to help!

[@Luciano_mp]

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

[@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?

[@praveenk1672]

Hello Mam, thanks for the tutorial. It will be helpful if similar logic can be arrived using formula. Because power query needs a refresh every time. Or is there a way to refresh the query automatically based on a scheduled time? We use excel for SLA calculation of tickets. In such cases, power query needs a refresh after each update. So, could you please make a tutorial based on formula in future.

[@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!!

[@alwarhi]

Many thanks 🥰

[@thetimmyboard6911]

Great video but I am looking for a formula that I can't seem to figure out. My guys can work up to 60 hours in a week. Every friday I run hours to see what type of hours they have left. Works great. What I am trying to figure out is what time they clock out if they clock in at a certain time. General speaking, Employee has logged 49 hours at the end of his shift on Thursday. For Friday he clocks in at 5am and has 11 hours to work, what time would he clock out in excel? Please!!!

[@musicaenlife]

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

[@LeilaGharani]

Thanks for watching!

[@rahulbhujbal3663]

Beauty with Brain, rare combination

[@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

[@fraziqbal6938]

wonderful. thank you very much!

[@ErosLuciano]

Hi Leila very useful video and as always, carefully explained. I would like to ask you if it is possible to implement this technique by enhancing the hours worked with different values ​​depending on whether they were worked after 8 hours (overtime e.g. + 25% for the 2 hours after 8 and + 50% beyond, i.e. 10 hours worked = 2 hours + 25%, 12 hours worked = 2 hours + 25% and 2 hours + 50%) also work on Saturdays and Sundays should have an increase of + 75%, at least this is what happens in Italy, thanks!

[@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!

[@hilaryjansz4687]

Great work

[@prasanjitdas1766]

Hi Leila, My question is not relevant to the following topic. But can I ask ? In case we can translate dynamic text from English language to Arabic language

[@sibidi894]

Excellent video as always Leila! A quick question: in the last table showing total hours worked by each person, I would like to show it in "days : hours : minutes : seconds". How would I do that? Thank you very much for all your videos.

[@aman9028206170]

Informative, Thanks Leila 🙂

[@jitu251105]

Excellent work but actually I wanted to know how to calculate in days if I restricted work from 9:00 am to 5:00 pm every day from 1/Jan/2021 to 10/Jan/2021. Please help me.

[@tharikum4289]

How to link the encrypted excel sheet using power query?

[@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

[@HusseinKorish]

Perfect ... Thanks Leila

[@tharindusilva6909]

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

[@rottemgolan8210]

Just noticed when I click my Time icon to perform the subtraction, I don't have the Subtract command! isn't that strange? any idea why? 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]

👍

[@SoZ4i]

Hi Leila, thanks for the tutorial, Is it possible for you to show an example of how to calculate % on on time/ late performance logistics based on quantity in Power Query example ? Thanks

[@MrPandiyan]

Hi Leila, I am not having option to subtract in the drop down.... Please help

[@mohamadhadid2403]

you are the best

[@shoaibrehman9988]

Really useful stuff no more formulas

[@LeilaGharani]

Glad it was helpful!

[@cshahed]

Leila a quick question, what if my week starts on Sat or Sun instead of Mon, how do I get the weekdays numbers?

[@jaimecalderon1432]

Thank you Leila

[@haank5053]

I want to mov vehicle columns and from time elapsed i want to calculate total time in date.... Can i do it For example A veh left at 1st Apr 8:00pm took 3 hrs B veh left 8:30pm took 4 hrs Running time by the time these veh reach destination