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
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!
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.
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! :-)
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!
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.
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
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...
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.
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)
How to calculate time taken for an instance. Where Working days are Monday to Friday and working time are 10 Am to 7 PM. For e.g First instance start time: 20 Aug 12 PM - End time: 25 Aug 11 Am. Manually if we calculate then the answer will come as 26 hrs.
Add two colums with time removed for start and end date. Calculate number of days between the two dates minus 2(the first and last day). Multiply that calculation by 9(working hours). Add two columns for the start and end time for the first and last days. Calculate the duration for those two days and add it to the previous calc where you multiplied the whole days by 9 hours.... might be an easier way but off the top of my head this should work.
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 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 🙏
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?
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 :)
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
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
Hi Leila ,Thanks for Sharing but the logic will work when you have combined format of ( Date &Time) 01/06/23 08:00 PM - 02/06/23 01:00 AM = 05 hrs 🎉 and what if available in below based Biometric devices out Put Start Date 01-06-2023 Start Time :20:00 End Time : 01:00 Result=???
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.
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
And you thought Leila is in CZcams only? You are so wrong!! she is in LinkedIn Learning too!! omg!!! I love her so much!!! She's everywhere!! "cause you're everywhere to me...when I close my eyes it's you I see..."
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.
Grab the file I used in the video from here 👉 pages.xelplus.com/calculate-time-file
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!
Mam, you're a blessing to the Excel community! 👌✨
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
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!
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.
OMG you are the Queen ! Thanks !
Yes! Yes she is. The queen of knowledge!
youre still the best!!! no one can beat you girl!!!
Very useful Video. Im just learning Power Query and creating good looking Dashboards in Excel. Your videos have really taken me further here.
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.
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 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.
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!
How can someone speak so beautifully!!!!!
Been working on this for so long, this is soo much easier than all the other methods. THANK YOU x
You're so welcome!
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 :)
Hi , this one is really good.... I admire your teaching skill's a lot . Keep doing the Good work..
Thank you, I will
Hello Leila you are a life saver 😘😘😘.
Happy to help!
Essential topic ,very nicely explained and simplified. Thanks a lot for sharing.
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.
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.
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
I wish i had known about this 8-9 years ago!!! Excellent video, Leila!!
Goodness...You really are good at this...Thanks
Great tip! Thanks
Thanks Leila for the work. wish you all the best
It's a great idea to use Power Query in time calculations. You can also get an age by simply using User Interface
👍
wonderful. thank you very much!
thanks for the video, very helpful, i was wondering how to do the basic time, undertime and overtime in this video
Superb information about date and time data
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...
This was so, so helpful
Happy to hear that!
Many thanks 🥰
Always useful tutorials. Thank you.
THANK YOU SO MUCH FOR THIS TUTORIAL!!!!!!!!!!!!!! YOU SAVED ME
Glad it helped!
Thanks Leila.... I can see these techniques being very helpful in many business situations. Great explanation.
I hope it will come in handy, Matt.
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.
very great video ♥ many thanks
Thank you. Very nice explanation.
Thanks Lelia. Very helpful as always
awesome video. thanks for sharing.
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!
Yes! Excellent lesson.
Very nice presentation Leila. I love this
Thanks Leila, this is very insightful
Beauty with Brain, rare combination
Wow so simple
Great thanks! Saving me for the second time today XD
Thank you! It`s awesome tutorial
Great work
I like the power query, good, Thank you Leila!
I love powerquery!
This is a useful one
Happy Thursday your highness 🙏
Glad to hear it! Happy Thursday to you too :)
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)
Thanks 🙏🙏
Excellent work and explanation Leila! I would appreciate more Power Query videos because they help a lot with data processing. Thank you very much!!
How to calculate time taken for an instance. Where Working days are Monday to Friday and working time are 10 Am to 7 PM. For e.g First instance start time: 20 Aug 12 PM - End time: 25 Aug 11 Am. Manually if we calculate then the answer will come as 26 hrs.
good question i'm looking for a solution to an equal problem
Add two colums with time removed for start and end date. Calculate number of days between the two dates minus 2(the first and last day). Multiply that calculation by 9(working hours). Add two columns for the start and end time for the first and last days. Calculate the duration for those two days and add it to the previous calc where you multiplied the whole days by 9 hours.... might be an easier way but off the top of my head this should work.
Happy Thursday Leila. Thanks...
Same to you! Thank you :)
Great session! Thank you very much.
Glad you enjoyed it!
you are the best
Great collection! Thank you for the videos.
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
You're the best ever
Thank you Leila
Perfect ... Thanks Leila
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
Another wonderful video Leila.. thank you..
Thank you too!
Miss Leila, please provide guidance on which project will take how many hours per week.
Great job Leila!
Thanks Chris 😊
Amazing ❤
Thanks for this Leila
Informative, Thanks Leila 🙂
Wow. Thanks
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
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 🙏
Really useful stuff no more formulas
Glad it was helpful!
Hey Leila you are awesome. Thanks for sharing great stuff..
Thanks for watching!
thanks.
It's Awesome Tricks, Thanks for sharing.
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?
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.
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
Thanks!
Super trick
This is great 👌 Thanks 😊
You're welcome 😊
I straight away hit LIKE. Lovely video and Lovely Leila :)
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
Mam, Thank you so much for the video.
Most welcome 😊
Very Wonderful 🤩
Oh Today is on Thursday.... 😜😜
I love your work Mom
😊 thank you
Hi Leila ,Thanks for Sharing but the logic will work when you have combined format of ( Date &Time) 01/06/23 08:00 PM - 02/06/23 01:00 AM = 05 hrs 🎉
and what if available in below based Biometric devices out Put
Start Date 01-06-2023
Start Time :20:00
End Time : 01:00
Result=???
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.
Excelente vídeo. Parabéns!
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
amazing
And you thought Leila is in CZcams only? You are so wrong!! she is in LinkedIn Learning too!! omg!!! I love her so much!!! She's everywhere!! "cause you're everywhere to me...when I close my eyes it's you I see..."
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.
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 😁
Perfect!