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

  Рет қаралды 172,418

Leila Gharani

Leila Gharani

Күн бұрын

Пікірлер: 216
@LeilaGharani
@LeilaGharani 9 ай бұрын
Grab the file I used in the video from here 👉 pages.xelplus.com/calculate-time-file
@kevinbolt999
@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
@wayneedmondson1065 4 жыл бұрын
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
@LeilaGharani 4 жыл бұрын
Glad you enjoyed it, Wayne!
@phpvbacoder9000
@phpvbacoder9000 4 жыл бұрын
Very useful Video. Im just learning Power Query and creating good looking Dashboards in Excel. Your videos have really taken me further here.
@AgesOfAgony
@AgesOfAgony 3 ай бұрын
Mam, you're a blessing to the Excel community! 👌✨
@Sunriseto
@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
@MrJrhzues Жыл бұрын
youre still the best!!! no one can beat you girl!!!
@ahs4494
@ahs4494 3 жыл бұрын
Always useful tutorials. Thank you.
@davidbecker4669
@davidbecker4669 3 жыл бұрын
OMG you are the Queen ! Thanks !
@StaticBlaster
@StaticBlaster 3 жыл бұрын
Yes! Yes she is. The queen of knowledge!
@fjeniferjeni
@fjeniferjeni 9 ай бұрын
Miss Leila, please provide guidance on which project will take how many hours per week.
@rachidmoutawakk1834
@rachidmoutawakk1834 4 жыл бұрын
Thanks Leila for the work. wish you all the best
@everclearacg
@everclearacg 4 жыл бұрын
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
@mariostudio7 4 жыл бұрын
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
@LeilaGharani 4 жыл бұрын
Yes, exactly! PQ is great!
@Ashley-ry4we
@Ashley-ry4we 2 жыл бұрын
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
@ericaleverson9430 3 жыл бұрын
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
@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
@felipelandim2881 4 жыл бұрын
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
@LeilaGharani 4 жыл бұрын
Excellent!
@azeezahmed7070
@azeezahmed7070 4 жыл бұрын
Essential topic ,very nicely explained and simplified. Thanks a lot for sharing.
@SUPERmuzhroom
@SUPERmuzhroom Жыл бұрын
thanks for the video, very helpful, i was wondering how to do the basic time, undertime and overtime in this video
@apolloistaphquibs5075
@apolloistaphquibs5075 Жыл бұрын
Hi Leila, do you have a link for google sheets on this? Thanks.
@praavi5444
@praavi5444 Жыл бұрын
THANK YOU SO MUCH FOR THIS TUTORIAL!!!!!!!!!!!!!! YOU SAVED ME
@LeilaGharani
@LeilaGharani Жыл бұрын
Glad it helped!
@wlouiz
@wlouiz 4 жыл бұрын
Thanks Lelia. Very helpful as always
@vijayarjunwadkar
@vijayarjunwadkar 4 жыл бұрын
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
@LeilaGharani 4 жыл бұрын
I've been there too :) Hope it will come in handy in the future.
@irkMukut
@irkMukut 4 жыл бұрын
How can someone speak so beautifully!!!!!
@gabriellaprievara7379
@gabriellaprievara7379 4 жыл бұрын
Thank you Leila, excellent as always. I do not know how, but your videos come always at the best time! :)
@LeilaGharani
@LeilaGharani 4 жыл бұрын
That's great to hear Gabriella :)
@eslamfahmy87
@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
@waltermlk 2 жыл бұрын
Goodness...You really are good at this...Thanks
@nonoobott8602
@nonoobott8602 4 жыл бұрын
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
@LeilaGharani 4 жыл бұрын
It really is :)
@LearnExcelVBA
@LearnExcelVBA 4 жыл бұрын
Thanks Leila for sharing this. This is surely going to help lot of employers and freelancers who regularly track time in excel.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
I hope it'll be helpful.
@cambike
@cambike 4 жыл бұрын
Been working on this for so long, this is soo much easier than all the other methods. THANK YOU x
@LeilaGharani
@LeilaGharani 4 жыл бұрын
You're so welcome!
@Jhun_L_Roxas
@Jhun_L_Roxas 2 жыл бұрын
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
@pedrohfcostapc 4 жыл бұрын
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
@shadrackawunyo7786 4 жыл бұрын
Very nice presentation Leila. I love this
@fcoatis
@fcoatis 4 жыл бұрын
Thank you. Very nice explanation.
@syamkumar-tc3xo
@syamkumar-tc3xo 4 жыл бұрын
Superb information about date and time data
@oladapoomolaja4104
@oladapoomolaja4104 4 жыл бұрын
Thanks Leila, this is very insightful
@shashwatmishra3023
@shashwatmishra3023 4 жыл бұрын
Hi , this one is really good.... I admire your teaching skill's a lot . Keep doing the Good work..
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thank you, I will
@rubencorderotorres2915
@rubencorderotorres2915 4 жыл бұрын
Thanks Leila, I'd like to know if power query could calculate working days, considering holy days and Saturday and sundays
@krn14242
@krn14242 4 жыл бұрын
Happy Thursday Leila. Thanks...
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Same to you! Thank you :)
@StaticBlaster
@StaticBlaster 4 жыл бұрын
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
@WJA5 4 жыл бұрын
I think I can use this to calculate length of service without using a formula...what are your thoughts? Thank you!
@bellomdragon1838
@bellomdragon1838 4 жыл бұрын
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
@OrcunBahadir 3 жыл бұрын
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
@alm6885 Жыл бұрын
Yes! Excellent lesson.
@aimendondo
@aimendondo 4 жыл бұрын
Great session! Thank you very much.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Glad you enjoyed it!
@aicoandaya1920
@aicoandaya1920 2 жыл бұрын
How can I make power query recognize the time and date properly?? really appreciate if you respond to my question. thank you
@chrism9037
@chrism9037 4 жыл бұрын
Great job Leila!
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks Chris 😊
@mattschoular8844
@mattschoular8844 4 жыл бұрын
Thanks Leila.... I can see these techniques being very helpful in many business situations. Great explanation.
@LeilaGharani
@LeilaGharani 4 жыл бұрын
I hope it will come in handy, Matt.
@wannabedal-adx458
@wannabedal-adx458 4 жыл бұрын
I wish i had known about this 8-9 years ago!!! Excellent video, Leila!!
@gmccormack1234
@gmccormack1234 4 жыл бұрын
Great collection! Thank you for the videos.
@zdzislawkes
@zdzislawkes 3 жыл бұрын
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
@hazemali382 4 жыл бұрын
very great video ♥ many thanks
@MultiSciGeek
@MultiSciGeek 2 жыл бұрын
Great thanks! Saving me for the second time today XD
@Jcook941
@Jcook941 4 жыл бұрын
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?
@Зле_Коте
@Зле_Коте 3 жыл бұрын
Thank you! It`s awesome tutorial
@RicardoSadaJapp
@RicardoSadaJapp 2 жыл бұрын
Great tip! Thanks
@karl2776
@karl2776 11 ай бұрын
This was so, so helpful
@LeilaGharani
@LeilaGharani 11 ай бұрын
Happy to hear that!
@brankahrehor6677
@brankahrehor6677 2 жыл бұрын
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
@ElectronicBarta 9 ай бұрын
awesome video. thanks for sharing.
@wofdigy
@wofdigy 4 жыл бұрын
Another wonderful video Leila.. thank you..
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thank you too!
@tonytaf107
@tonytaf107 2 жыл бұрын
Any suggestions to covert for example: 1m 30s to 1.5 or 4m 30s to 4.5 ???? Please
@LifeofSheru
@LifeofSheru 4 жыл бұрын
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
@moazzamca1 4 жыл бұрын
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
@abdallah.kandiel 4 жыл бұрын
This is a useful one Happy Thursday your highness 🙏
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Glad to hear it! Happy Thursday to you too :)
@shahidahammed478
@shahidahammed478 4 жыл бұрын
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
@Deependra1991 4 жыл бұрын
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
@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
@dhrumildarji683 2 жыл бұрын
Can you please tell me how to calculate Time in hh:mm:ss format when Distance and speed is available
@gintomino4136
@gintomino4136 4 жыл бұрын
I love powerquery!
@ibrahim82649
@ibrahim82649 Жыл бұрын
Hello Leila you are a life saver 😘😘😘.
@LeilaGharani
@LeilaGharani Жыл бұрын
Happy to help!
@Luciano_mp
@Luciano_mp 4 жыл бұрын
I like the power query, good, Thank you Leila!
@stevejobs5533
@stevejobs5533 2 жыл бұрын
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
@praveenk1672 4 жыл бұрын
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
@IvanCortinas_ES 4 жыл бұрын
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
@alwarhi 4 жыл бұрын
Many thanks 🥰
@thetimmyboard6911
@thetimmyboard6911 3 жыл бұрын
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
@musicaenlife 4 жыл бұрын
Hey Leila you are awesome. Thanks for sharing great stuff..
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Thanks for watching!
@rahulbhujbal3663
@rahulbhujbal3663 3 жыл бұрын
Beauty with Brain, rare combination
@serawitbogale3144
@serawitbogale3144 4 жыл бұрын
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
@fraziqbal6938 Жыл бұрын
wonderful. thank you very much!
@ErosLuciano
@ErosLuciano 4 жыл бұрын
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
@darrylmorgan 4 жыл бұрын
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
@LeilaGharani 4 жыл бұрын
My pleasure Darryl!
@hilaryjansz4687
@hilaryjansz4687 11 ай бұрын
Great work
@prasanjitdas1766
@prasanjitdas1766 4 жыл бұрын
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
@sibidi894 4 жыл бұрын
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
@aman9028206170 4 жыл бұрын
Informative, Thanks Leila 🙂
@jitu251105
@jitu251105 2 жыл бұрын
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
@tharikum4289 4 жыл бұрын
How to link the encrypted excel sheet using power query?
@imransadiq5744
@imransadiq5744 3 жыл бұрын
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
@HusseinKorish 4 жыл бұрын
Perfect ... Thanks Leila
@tharindusilva6909
@tharindusilva6909 2 жыл бұрын
Hi Eeila I need a help! How to calculate the total hours if end date & time is less than the start date & time?
@rottemgolan8210
@rottemgolan8210 2 жыл бұрын
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
@vida1719 4 жыл бұрын
It's a great idea to use Power Query in time calculations. You can also get an age by simply using User Interface
@LeilaGharani
@LeilaGharani 4 жыл бұрын
👍
@SoZ4i
@SoZ4i 2 жыл бұрын
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
@MrPandiyan 4 жыл бұрын
Hi Leila, I am not having option to subtract in the drop down.... Please help
@mohamadhadid2403
@mohamadhadid2403 Жыл бұрын
you are the best
@shoaibrehman9988
@shoaibrehman9988 4 жыл бұрын
Really useful stuff no more formulas
@LeilaGharani
@LeilaGharani 4 жыл бұрын
Glad it was helpful!
@cshahed
@cshahed 4 жыл бұрын
Leila a quick question, what if my week starts on Sat or Sun instead of Mon, how do I get the weekdays numbers?
@jaimecalderon1432
@jaimecalderon1432 4 жыл бұрын
Thank you Leila
@haank5053
@haank5053 2 жыл бұрын
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
Properly Convert PDF to Excel
11:28
Leila Gharani
Рет қаралды 1 МЛН
Stop Wasting Time! 3 Easy Ways to Remove Blank Rows in Excel
10:20
Leila Gharani
Рет қаралды 121 М.
Это было очень близко...
00:10
Аришнев
Рет қаралды 7 МЛН
Will A Basketball Boat Hold My Weight?
00:30
MrBeast
Рет қаралды 148 МЛН
Simple Time Sheet In Excel
12:06
EZ Learning
Рет қаралды 1,1 МЛН
You WON'T BELIEVE These 10 HIDDEN Features in Excel Power Query 🤯
12:17
Common Excel Pivot Table Features People Miss (and you?)
12:45
Leila Gharani
Рет қаралды 153 М.
Make Beautiful Excel Charts Like The Economist (file included)
19:07
Leila Gharani
Рет қаралды 468 М.
When Should You Use the Hash Sign in Excel Formulas?
10:53
Leila Gharani
Рет қаралды 1,1 МЛН
Это было очень близко...
00:10
Аришнев
Рет қаралды 7 МЛН