How to Calculate Hours Worked with Excel Power Query (& Properly Sum time)
ฝัง
- เผยแพร่เมื่อ 31 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Working with time in Excel or to calculate Hours Worked especially when they go past midnight can get tricky in Excel. Forget about using complex Excel formulas! In this video I'll show you how you can use Excel Power Query and its very helpful time transformation features.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/calculate-t...
Working with time has never been easier! With just a click of a few buttons you can:
- Calculate the difference between two times
- How to sum time in Excel
- Subtract time
- Calculate hours worked for any shift
- Round up to the hour
- Get the hour, minute or second portion of time
🎓 Get access to the complete Excel Power Query Course: www.xelplus.com/course/excel-...
Link to Excel Power Query playlist: • Excel Power Query (Get...
★ My Online Excel Courses ► www.xelplus.com/courses/
00:00 Working with Time in Excel
01:49 Time Transformation in Excel Power Query
04:59 Add Weekday in Excel Power Query
06:42 Aggregating Hours Worked with Excel Group By
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #powerquery
Grab the file I used in the video from here 👉 pages.xelplus.com/calculate-time-file
Mam, you're a blessing to the Excel community! 👌✨
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.
Excellent work and explanation Leila! I would appreciate more Power Query videos because they help a lot with data processing. Thank you very much!!
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...
I wish i had known about this 8-9 years ago!!! Excellent video, Leila!!
Essential topic ,very nicely explained and simplified. Thanks a lot for sharing.
Great collection! Thank you for the videos.
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
Very useful Video. Im just learning Power Query and creating good looking Dashboards in Excel. Your videos have really taken me further here.
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!
Glad you enjoyed it, Wayne!
youre still the best!!! no one can beat you girl!!!
I like the power query, good, Thank you Leila!
Thanks Leila for the work. wish you all the best
Always useful tutorials. Thank you.
Been working on this for so long, this is soo much easier than all the other methods. THANK YOU x
You're so welcome!
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! :-)
I've been there too :) Hope it will come in handy in the future.
Thanks Leila, this is very insightful
wonderful. thank you very much!
Great tip! Thanks
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.
Thanks Lelia. Very helpful as always
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.
Yes! Excellent lesson.
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
It really is :)
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
Informative, Thanks Leila 🙂
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!
Excellent!
Thank you. Very nice explanation.
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 :)
Great to hear! Thanks for your support.
Thank you Leila, excellent as always. I do not know how, but your videos come always at the best time! :)
That's great to hear Gabriella :)
I straight away hit LIKE. Lovely video and Lovely Leila :)
Thanks Leila for sharing this. This is surely going to help lot of employers and freelancers who regularly track time in excel.
I hope it'll be helpful.
Thank you! It`s awesome tutorial
Excelente vídeo. Parabéns!
You're the best ever
Perfect ... Thanks Leila
Goodness...You really are good at this...Thanks
Thanks Leila.... I can see these techniques being very helpful in many business situations. Great explanation.
I hope it will come in handy, Matt.
OMG you are the Queen ! Thanks !
Yes! Yes she is. The queen of knowledge!
very great video ♥ many thanks
awesome video. thanks for sharing.
Thanks for this Leila
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 😍
Yes, exactly! PQ is great!
Great session! Thank you very much.
Glad you enjoyed it!
Many thanks 🥰
It's Awesome Tricks, Thanks for sharing.
Very nice presentation Leila. I love this
Great thanks! Saving me for the second time today XD
Superb information about date and time data
I love powerquery!
This was so, so helpful
Happy to hear that!
Another wonderful video Leila.. thank you..
Thank you too!
Thank you Leila
Great work
THANK YOU SO MUCH FOR THIS TUTORIAL!!!!!!!!!!!!!! YOU SAVED ME
Glad it helped!
Thanks!
Wow. Thanks
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
Hey Leila you are awesome. Thanks for sharing great stuff..
Thanks for watching!
Amazing ❤
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
Thanks 🙏🙏
Perfect!
you are the best
Great job Leila!
Thanks Chris 😊
Wow so simple
Hi Leila!I Have Set This Same Scenario Up For Project Managers In Work Using Power Query...Thank You For A Really Enjoyable Tutorial :)
My pleasure Darryl!
thanks for the video, very helpful, i was wondering how to do the basic time, undertime and overtime in this video
Hello Leila you are a life saver 😘😘😘.
Happy to help!
Happy Thursday Leila. Thanks...
Same to you! Thank you :)
Coooooool👍
Hi , this one is really good.... I admire your teaching skill's a lot . Keep doing the Good work..
Thank you, I will
Very Wonderful 🤩
This is a useful one
Happy Thursday your highness 🙏
Glad to hear it! Happy Thursday to you too :)
How can someone speak so beautifully!!!!!
This is great 👌 Thanks 😊
You're welcome 😊
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
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.
Mam, Thank you so much for the video.
Most welcome 😊
thanks.
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.
amazing
Super trick
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?
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
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)
Muito Muito Bom
Beauty with Brain, rare combination
Thank you Leila! Is there a way to do SUMIFS and AVERAGEIFS with PQ? If there is could you make a video about those?
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!
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.
Thanks Leila, I'd like to know if power query could calculate working days, considering holy days and Saturday and sundays
Genial..
I lrean a new leason
Thank you 😍
Wow!
It's a great idea to use Power Query in time calculations. You can also get an age by simply using User Interface
👍
It's about time somebody explained this. Now excuse me while I calculate how long I've been waiting.
No, you can't do that. It's about hours WORKED 😁
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?
Oh Today is on Thursday.... 😜😜
I love your work Mom
😊 thank you