Excel Formula for Time Elapsed in Days, Hours and Minutes
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- Calculate the time elapsed in days, hours and minutes between two date and times entered into Excel.
Excel stores date and times as a number with decimals. Because of this we cannot simply subtract the two values.
This video tutorial uses a combination of functions to calculate the time elapsed and display it in an easy to understand format. The INT and MOD functions play important roles in making this happen.
★★ Liked the Video? Subscribe: bit.ly/computergaga ★★
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 - แนวปฏิบัติและการใช้ชีวิต
![[LIVE] : ONE ลุมพินี 71 วันนี้!! คู่เอก "ทรงชัยน้อย vs รักษ์"](http://i.ytimg.com/vi/_AGPnq2p6Js/mqdefault.jpg)
![ตีสิบเดย์ [FULL] | คู่รักแซฟฟิก "หลิงหลิง - ออม" จากซีรีย์ "ใจซ่อนรัก"](http://i.ytimg.com/vi/zs8AfphGn2w/mqdefault.jpg)
This is the best video I have watched for Time calculations in Days & Hours. Thanks a lot.
Thank you very much, Tony.
Thank you for this Tutorial really helpful. I don't want to include hours after shift (22:00 - 07:00). how do I factor the 9 hours out?
Hi, Is there a way to return the answer with leading zeros, say,- 3 days 9 hours 5 minutes as 3d 09h 05m (or 3:09:05:00 if you use colons (and include seconds if you prefer) instead of d,h,m,s)
=INT(B2-A2)&" Days"&Hour(MOD(B2-A2,1))&" Hours "&MINUTE(MOD(B2-A2,1))&" minutes"
what will be the formula if I want to exclude the weekend? Please help..
You would use NETWORKDAYS or NETWORKDAYS.INTL instead of B2-A2.
@@Computergaga Hello Sir please type Full formula with exclude weekend and I want result in days Hour minutes
Good Day. How can I show days only. So for example I only want to reflect full days.
Or you can also use this formula as well, it's very similar. But if there's no data in it it'll show blank.
=If(B2="","",INT(B2-A2)&" Days "&TEXT(B2-A2, "h""hrs""m""mins"""))
The format in the text you can change it to whatever you wanna see.
@Computergaga Thanks for sharing this. what will be the formula if I want to exclude the weekend? Please help..
is it possible to incorporate =IF(A2
Hi, How do you Add in the ability to also measure Years and Months whilst still keeping all the information above? thanks
Sir your education is like a burning lamp. Who shows the right path. We are very happy with you.
That is beautiful, Sandeep. Thank you 😊
Very important & useful lesson once again, thank you so much!
You're welcome Carlos. Thank you.
Sir, how would I be able to have the values be based on a 8 hour work day that would auto subtract from the days (I am applying it to employee accruals/used leave) Thanks
Well explained, thank you, keep up the good work!!
I never comment on videos but this was just great and helped me resolve my issue. Thanks 🙏
That's awesome. Thank you, Akhmad.
Good Job Explaining. Finally got it after adjusting the cell formatting!
Thank you! Glad that it helped.
Great and easy way of explaining the
topic, thank you very much.
You're very welcome.
Is there a way to do this with CONCATENATE? Ex: A2 is 8/2/2021, B2 is 8:30 and A3 is 8/4/2021,B3 is 4:30. Once concatenated it joins the cells of A2 and B2 into C2 and A3 and B3 into C3. I've tried to do this but it comes out not looking right. is it impossible to separate the date and time?
(sorry if it's a confusing question)
Respected sir,
I m Dhaval from india. Finding this kind of formula. This is a wonderful. Just complete solution. Love know more & more. Thank you very much And respecte. Manaskara.
Thank you Dhaval for your kind words.
@@Computergaga
Mostly well come
Sir. Have good day
HI, is it possible to calculate the next date and time with counting 8 hrs per day.
Nice one! Life saver. Found this video 10 minutes before my meeting and quickly updated my spreadsheet! Liked!
Is there a way to hide the value if it is zero because the date and time values have not been entered yet?
Sure. We can do this in the formatting of the cells. Difficult to explain in the comment, but zero values are the third element in custom formatting. If you ignore it, it will display as nothing.
We could also wrap and IF around this to achieve it.
Once you get the Days, hours and minutes, is there any way to sun this elasped time? Thx
Excellent and very informative video!
Well explained! Thanks - exactly what I needed!!
Thank you, Maria.
I have a question not related with the video.
But is it possible too sum values from one cell but only have it updated to the current cell when that value is added in?
Soo for example if I have a formula to calculate equity in all of row "A", and say the equity is at $200 in A18, and say I wanna add a deposit of $100 in column B1.
Is it possible just to update the current column in A18, rather than suming the rest of the columns in "A1-A17."
Soo column A18 would show $300, and the rest of the columns from A1-A17 values would stay the same?
I have noo idea if you can or can't doo this in excel.
I know for a fact tho, is if I match the same columns and rows in column A and B than it'll work perfectly. But just wondering if I can do it that way I mentioned above?
Good work. I managed to adapt that into a calculated column and it works a treat. I wonder what the DAX equivalent of that in the data modeler is, in Power pivot?
Something I have not tried. But I am sure the equivalent is possible there. Lots to explore in DAX.
so elegant sir
I keep having problems with the mod function between the cells.Any help?
Brilliant! Thank you!!
My pleasure.
Great video, how would I calculate time lapsed between two date/time values based on a working day , for example calculating the difference between core working hours say 8.30am-5.00pm
Thank you Huma. I have a video here - th-cam.com/video/3bUVySH5prQ/w-d-xo.html that explains this and more. It is a timesheet spreadsheet based on different work hours and days.
hello, sir when I apply this formula, hours and minutes are showing perfectly but day is not showing. it shows something weird? how solve this. and one more thing when adding zero before day and months when shifting to another cell the zero disappears how to solve this as well?
What is formula if calculate as per (8hrs)as working hrs?
Thank You
Thank you sir. You are best a best teacher I am very happy .may you live long.
Thank you Sandeep.
This is excellent! Thank you so much.
Can you please advise how to include the networkdays as well to this. It will be really helpful.
Thank you, Karishma. NETWORKDAYS would replace the B2-A2 calculation.
@@Computergaga but how? Can you show the formula based on your video? Thank you 🙏
If , i use different column like start date, start time, end date ,end time... What is the formula??
Thanks you for your very informative video! But I do have a question, how would you add all of your differences together afterwards?
Did you ever figure out how to add them all up?
@@MuddyBuddy685 unfortunately, no
Erim Wolf what did you end up doing with your worksheet? I know it was a year ago but I’m hoping for som help
@@MuddyBuddy685 I did actually! you add up the rows containing your dates instead of the new totals you acquired in your row.
for example: =INT((SUM(B2:B3))-(SUM(A2:A3)))&"days"&HOUR(MOD((SUM(B2:B3))-(SUM(A2:A3)),1))&"hours"&MINUTE(MOD((SUM(B2:B3))-(SUM(A2:A3)),1))&"minutes"
(I input those specific numbers to go with the video so you have a visual, but replace the number with whatever you have) :)
@@erimwolf7285 Great! Thank you so much!
Perfect!! Thank you.
You're welcome Vinesh.
Thanks alot for the teaching sir... It's very helpful
Great to hear. Thank you.
hi, How to sum up days, hours and minutes?
Very Informative and Easy to Understand
Thank you, Iftiquar.
Awesome!!! you saved my day...
Awesome. Thank you Giri.
How to exclude Downtime or Non-working hours from two different dates & time while calculating Time Spent? Please help.
Thanks. Was very helpful!
Glad to hear it! Thank you.
Thank you dear but I have one more to clear, how to calculate the average of both difference day and time?
Hi Computer Gaga, I have been looking for this formula for days! I am so glad I found your chanel, I have subscribed and currently on your playlist.
My question for this tutorial is, can the formula work if the difference between A and B is in hours e.g 3 hours and when the time in B increases,it displays 3 days, 3 hours?
Thank you. Sure. We could wrap the formula in a conditional function so that if the value is over a day then show days, otherwise just the hours.
@@Computergaga can you actuall show how to do this? as well as showing how to average these types of datas?
Very helpful, thank you!
Glad it was helpful!
Hi this is really useful. What changes would I have to make to the formula to display the time elapsed in hours?
Thank you, Marc. I have another video on techniques for this here - th-cam.com/video/_Fg4gl4qvss/w-d-xo.html
Thankyou, this helped me out!
Great to hear, Taione. Thank you.
Thanks for sharing this forumal. Complex but I learnt a lot. Now.... What if we want to show the result in WORKING DAYS (Mon-Fri) and TIMES (9-5PM)
Thank you so much for this. Really helpful
You're very welcome!
Thanks for a great video! What do you mean in your other comments to swap b2-a2 with networkdays to remove weekends. So confused! 🥺
Thank you. B2-A2 returns all days between two dates. NETWORKDAYS is a function that returns only the working days. It allows you to specify the weekend days and any other additional non-working days such as holidays, illness, other projects etc. This function could be inserted instead if people wanted working days only.
How can I calculate the elapsed time in days hrs and mins as just minutes
hello sir, when is use this formula days, hours ,and minutes shows but days are not showing as it appears on your screen. could please tell me how to solve and why?
How can you get average of the two to demonstrate day, hour and minutes
Thanks for the tutorial..
You're welcome, Pradeep.
How do you get the date and time to be displayed in a single cell?
And can you edit that particular cell, i.e. can you enter "11/10/19 10:13:42" in another cell?
Sure. You can enter that in a cell.
Brilliant !!!
Thank you Zahin.
Is it possible to get the average of the result?
now how r we supposed to plot it?
Good job done you Sir, too much helpful you video sir........
Thanks and you're welcome, Hammad.
Thanku sir ,its very help full for me .
Great to hear Ankita. I'm happy to help.
How to calculate the difference between 2 date/time ranges in business days?
For example, cell A2: 07-Jul-23 18:00hrs and B2: 09-Jul-23 10:00hrs
The result in C2 should reflect "00 Days 06 Hours" since Saturday and Sunday must be completely excluded in the calculation.
I tried all possible options including chat GPT but failed in getting the formula😢
great video thank you
Thank you Jay.
Thank You!!!!!
You're welcome Crystal.
Very helpful , Thanks
You're welcome 👍👍
This is really helpful
Great to hear 👍 Thanks.
Hi, Great video....is there an easy way to show days hrs and mins from the calculation in separate cells?
Sure. Instead of combining the results, you can write separate formulas for each cell/column.
how do i exclude weekends from this?
👏🏻👏🏻👍🏻👍🏻 was so helpful
I'm so glad! Thank you, Jelveh.
Thank you so much
You're most welcome
Thanks ur such a big help ☺️
You're welcome. Happy to help.
How do I get the negatives to appear red. I'm not sure how to build the custom formatting
In custom formatting, whatever you type after the first semicolon is the negative formatting. So you could enter something like 0;[Red]-0 for negative numbers to have red and a minus sign before them.
You can see this on this video - th-cam.com/video/OXw5X-HMwiU/w-d-xo.html
Thank you
Welcome!
Good job.
Thank you very much Daniel.
How would you be able to get the average duration for multiple entries using your format?
This technique provides an answer formatted as text so the average is not possible. You would need a different technique to calculate the time elapsed.
Wow you are amazing
Thank you 😊
Excellent
Thank you Majid.
This is great but it isn’t working on my sheet 🙈 typed exactly like yours, same spaces and everything. Any advice? Could it be that my baby excel version is too old?
You version will be fine. This function is not new. I would check the formula again. There will be a difference. Do you receive a specific error? Are you based in Europe, as you may need ; instead of ,?
Nice information
Thanks Zaman.
Thanks 👍
You're welcome.
Ctrl+Shift+Alt+; Shortcut key works on that formula .
Help me a lot!
Awesome!
thanks a lot
You're very welcome, Vasilis.
Awesome
Thank you.
Perfect! Thank you.
My pleasure Todd.
please upload some other advance videos on indirect function
No problem Jacky. Will have a video soon.
thank you sir ________
What if the "end date" is blank or not filled, What will be the formula?
Hello, I have a question? After we get the "Difference" in: Days Hours Minutes, how can we get the AVERAGE of those "Differences" in: Days Hours Minutes as well, because it gives me an error when I use AVERAGE.
Yes the answer contains text strings such as " days" so AVERAGE cannot work with it. You would need to calculate the difference (could be a different column) with just simple B2-A2 formula. Then use the formula from the video on the AVERAGE answer from those responses to tidy it up into days, hours and minutes.
Thank you so much, that will work great! Thanks for all your lessons, they make a huge difference in the way I use Excel!!!
A.o.A sir, koi esa formula ha jis ka through previous time sa current time less ho kar show ho or jub usa time stop ki command da to time counter stop ho jaye, agar esi koi video ha toh recommend kare kindly.....??
What if in this example the end date is before the start date. It's for a planned and actual date and time for an event happening
Maybe test it with an IF first to check if the end date is later or not. Then you will have the option of doing the formula like in the video, or in reverse to show being ahead of schedule.
how can i solve time subtracting when i subtracted 10:00 AM and 10:20 AM i want to get 20 minutes in result but i get 23:40
It should be fine. If 10:20 was in cell A1 and 10:00 was in cell A2 then = A1-A2 is 00:20
Can you do this with 4 date and time entries instead of 2?
Sure. You could find the difference between any dates you. Obviously difference is always between two things, but then you could add the difference between two others or whatever your goal is.
works brilliantly but is there a way to have the result update automatically? thanks
Great! But the closest you can get to automatic is some VBA code to run on intervals.
Thanks
You're welcome, Sajeer.
Is there a way to total the difference column?
The result here is text so cannot be totalled. You would need to total the separate elements (days, hours etc) separately to get your total.
THANKS BUDDY, THIS ONE IS SAFE MY JOB😅😅😅
My pleasure!
Does not work for me. Most probably not the correct data format for the date & time cells. What do you suggest? Thanks
Select your date cells, and separately your time cells. and check the bottom right on your screen for a Sum. That will help check if there are identified as dates and times.
@@Computergaga I have date and time in the same cell as from your example. in the bottom right display sum; date has no sense while hours are a correct sum
After getting days and hour how can we less 6 hours in this formula?
great work
Thank you Ibrahim.
@@Computergaga I have a question...my Job includes calculating the arrival date and time of a vessel and the departure date and time..I should calculate number of days and time the vessel stay in port..but i find it very difficult...thanks
Did the video not work for you? What trouble are you having?
@@Computergaga yes it helps but you enter both the date and time on the same cell which i normally don't use. buh am checking you previous videos whether i will see the one that suites me.
buh is great help thank
If it helps Ibrahim, you could create a 'helper column' of dae and time by adding the 2 cells you have together. And then use that helper column in the formula. This helper column can be hidden and created solely for the calculation.
This is great but you might have included in a PINNED COMMENT the FUNCTION LINE for us to copy. Naturally we'll learn more your way but would have made things faster if we just need it done.
Why would my result differ with exactly the same values. (88 Days23 Hours 50 minutes
)
Abrie i think you have the US date format not the UK format so it thinks you are doing between the 1st Feb 2017 and the 1st May 2017. UK format is 2nd Jan to 5th Jan 2017
What format should you use for the dates and times?
The dates and times can be in any date and time format. As long as Excel recognises it as a date and time. The answer is text.