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
@wayneedmondson10654 жыл бұрын
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!
@LeilaGharani4 жыл бұрын
Glad you enjoyed it, Wayne!
@phpvbacoder90004 жыл бұрын
Very useful Video. Im just learning Power Query and creating good looking Dashboards in Excel. Your videos have really taken me further here.
@AgesOfAgony3 ай бұрын
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!!!
@ahs44943 жыл бұрын
Always useful tutorials. Thank you.
@davidbecker46693 жыл бұрын
OMG you are the Queen ! Thanks !
@StaticBlaster3 жыл бұрын
Yes! Yes she is. The queen of knowledge!
@fjeniferjeni9 ай бұрын
Miss Leila, please provide guidance on which project will take how many hours per week.
@rachidmoutawakk18344 жыл бұрын
Thanks Leila for the work. wish you all the best
@everclearacg4 жыл бұрын
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.
@mariostudio74 жыл бұрын
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 😍
@LeilaGharani4 жыл бұрын
Yes, exactly! PQ is great!
@Ashley-ry4we2 жыл бұрын
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?
@ericaleverson94303 жыл бұрын
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
@felipelandim28814 жыл бұрын
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!
@LeilaGharani4 жыл бұрын
Excellent!
@azeezahmed70704 жыл бұрын
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!
@wlouiz4 жыл бұрын
Thanks Lelia. Very helpful as always
@vijayarjunwadkar4 жыл бұрын
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! :-)
@LeilaGharani4 жыл бұрын
I've been there too :) Hope it will come in handy in the future.
@irkMukut4 жыл бұрын
How can someone speak so beautifully!!!!!
@gabriellaprievara73794 жыл бұрын
Thank you Leila, excellent as always. I do not know how, but your videos come always at the best time! :)
@LeilaGharani4 жыл бұрын
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 🙏
@waltermlk2 жыл бұрын
Goodness...You really are good at this...Thanks
@nonoobott86024 жыл бұрын
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
@LeilaGharani4 жыл бұрын
It really is :)
@LearnExcelVBA4 жыл бұрын
Thanks Leila for sharing this. This is surely going to help lot of employers and freelancers who regularly track time in excel.
@LeilaGharani4 жыл бұрын
I hope it'll be helpful.
@cambike4 жыл бұрын
Been working on this for so long, this is soo much easier than all the other methods. THANK YOU x
@LeilaGharani4 жыл бұрын
You're so welcome!
@Jhun_L_Roxas2 жыл бұрын
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?
@pedrohfcostapc4 жыл бұрын
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.
@shadrackawunyo77864 жыл бұрын
Very nice presentation Leila. I love this
@fcoatis4 жыл бұрын
Thank you. Very nice explanation.
@syamkumar-tc3xo4 жыл бұрын
Superb information about date and time data
@oladapoomolaja41044 жыл бұрын
Thanks Leila, this is very insightful
@shashwatmishra30234 жыл бұрын
Hi , this one is really good.... I admire your teaching skill's a lot . Keep doing the Good work..
@LeilaGharani4 жыл бұрын
Thank you, I will
@rubencorderotorres29154 жыл бұрын
Thanks Leila, I'd like to know if power query could calculate working days, considering holy days and Saturday and sundays
@krn142424 жыл бұрын
Happy Thursday Leila. Thanks...
@LeilaGharani4 жыл бұрын
Same to you! Thank you :)
@StaticBlaster4 жыл бұрын
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.
@WJA54 жыл бұрын
I think I can use this to calculate length of service without using a formula...what are your thoughts? Thank you!
@bellomdragon18384 жыл бұрын
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
@OrcunBahadir3 жыл бұрын
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.
@aimendondo4 жыл бұрын
Great session! Thank you very much.
@LeilaGharani4 жыл бұрын
Glad you enjoyed it!
@aicoandaya19202 жыл бұрын
How can I make power query recognize the time and date properly?? really appreciate if you respond to my question. thank you
@chrism90374 жыл бұрын
Great job Leila!
@LeilaGharani4 жыл бұрын
Thanks Chris 😊
@mattschoular88444 жыл бұрын
Thanks Leila.... I can see these techniques being very helpful in many business situations. Great explanation.
@LeilaGharani4 жыл бұрын
I hope it will come in handy, Matt.
@wannabedal-adx4584 жыл бұрын
I wish i had known about this 8-9 years ago!!! Excellent video, Leila!!
@gmccormack12344 жыл бұрын
Great collection! Thank you for the videos.
@zdzislawkes3 жыл бұрын
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
@hazemali3824 жыл бұрын
very great video ♥ many thanks
@MultiSciGeek2 жыл бұрын
Great thanks! Saving me for the second time today XD
@Jcook9414 жыл бұрын
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
@RicardoSadaJapp2 жыл бұрын
Great tip! Thanks
@karl277611 ай бұрын
This was so, so helpful
@LeilaGharani11 ай бұрын
Happy to hear that!
@brankahrehor66772 жыл бұрын
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?
@ElectronicBarta9 ай бұрын
awesome video. thanks for sharing.
@wofdigy4 жыл бұрын
Another wonderful video Leila.. thank you..
@LeilaGharani4 жыл бұрын
Thank you too!
@tonytaf1072 жыл бұрын
Any suggestions to covert for example: 1m 30s to 1.5 or 4m 30s to 4.5 ???? Please
@LifeofSheru4 жыл бұрын
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?
@moazzamca14 жыл бұрын
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.kandiel4 жыл бұрын
This is a useful one Happy Thursday your highness 🙏
@LeilaGharani4 жыл бұрын
Glad to hear it! Happy Thursday to you too :)
@shahidahammed4784 жыл бұрын
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?
@Deependra19914 жыл бұрын
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
@dhrumildarji6832 жыл бұрын
Can you please tell me how to calculate Time in hh:mm:ss format when Distance and speed is available
@gintomino41364 жыл бұрын
I love powerquery!
@ibrahim82649 Жыл бұрын
Hello Leila you are a life saver 😘😘😘.
@LeilaGharani Жыл бұрын
Happy to help!
@Luciano_mp4 жыл бұрын
I like the power query, good, Thank you Leila!
@stevejobs55332 жыл бұрын
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?
@praveenk16724 жыл бұрын
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_ES4 жыл бұрын
Excellent work and explanation Leila! I would appreciate more Power Query videos because they help a lot with data processing. Thank you very much!!
@alwarhi4 жыл бұрын
Many thanks 🥰
@thetimmyboard69113 жыл бұрын
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!!!
@musicaenlife4 жыл бұрын
Hey Leila you are awesome. Thanks for sharing great stuff..
@LeilaGharani4 жыл бұрын
Thanks for watching!
@rahulbhujbal36633 жыл бұрын
Beauty with Brain, rare combination
@serawitbogale31444 жыл бұрын
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!
@ErosLuciano4 жыл бұрын
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!
@darrylmorgan4 жыл бұрын
Hi Leila!I Have Set This Same Scenario Up For Project Managers In Work Using Power Query...Thank You For A Really Enjoyable Tutorial :)
@LeilaGharani4 жыл бұрын
My pleasure Darryl!
@hilaryjansz468711 ай бұрын
Great work
@prasanjitdas17664 жыл бұрын
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
@sibidi8944 жыл бұрын
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.
@aman90282061704 жыл бұрын
Informative, Thanks Leila 🙂
@jitu2511052 жыл бұрын
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.
@tharikum42894 жыл бұрын
How to link the encrypted excel sheet using power query?
@imransadiq57443 жыл бұрын
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
@HusseinKorish4 жыл бұрын
Perfect ... Thanks Leila
@tharindusilva69092 жыл бұрын
Hi Eeila I need a help! How to calculate the total hours if end date & time is less than the start date & time?
@rottemgolan82102 жыл бұрын
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!
@vida17194 жыл бұрын
It's a great idea to use Power Query in time calculations. You can also get an age by simply using User Interface
@LeilaGharani4 жыл бұрын
👍
@SoZ4i2 жыл бұрын
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
@MrPandiyan4 жыл бұрын
Hi Leila, I am not having option to subtract in the drop down.... Please help
@mohamadhadid2403 Жыл бұрын
you are the best
@shoaibrehman99884 жыл бұрын
Really useful stuff no more formulas
@LeilaGharani4 жыл бұрын
Glad it was helpful!
@cshahed4 жыл бұрын
Leila a quick question, what if my week starts on Sat or Sun instead of Mon, how do I get the weekdays numbers?
@jaimecalderon14324 жыл бұрын
Thank you Leila
@haank50532 жыл бұрын
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