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.
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.
You're welcome. For that, we would need a column instead or in addition to this one, with the difference as a numeric value. We cannot average the results from this video as they are a text string. A simple subtraction such as B2-A2 would achieve this, though if dates and times are in different columns, they need to be combined first. Then you can average the results of the column.
@@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) :)
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.
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 - czcams.com/video/3bUVySH5prQ/video.html that explains this and more. It is a timesheet spreadsheet based on different work hours and days.
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?
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.
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?
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?
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?
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.
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 ,?
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
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)
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?
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)
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?
@@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
@@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.
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 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😢
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.
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.....??
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.
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 - czcams.com/video/OXw5X-HMwiU/video.html
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.
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
B2=Date 1=2020-07-03 22:59:55 C2=Date 2=2020-07-02 15:14:22 A2 =Text(B2-C2,"dd:hh:mm:ss") =01:07:45:33 solution1: DAY(LEFT(TEXT(B2-C2,"dd:hh:mm:ss"),2))+TIME(MID(TEXT(B2-C2,"dd:hh:mm:ss"),4,2),MID(TEXT(B2-C2,"dd:hh:mm:ss"),7,2),RIGHT(TEXT(B2-C2,"dd:hh:mm:ss"),2)) or Soln 2 : DAY(LEFT(TEXT(A2,"dd:hh:mm:ss"),2))+TIME(MID(TEXT(A2,"dd:hh:mm:ss"),4,2),MID(TEXT(A2,"dd:hh:mm:ss"),7,2),RIGHT(TEXT(A2,"dd:hh:mm:ss"),2)) Result: '=1143333 if format to [ss] '=01:07:45:33 if format to dd:hh:mm:ss '=31:45:33 if format to [h]:mm:ss but whichever format is used in the result, u can apply average function now.
This is the best video I have watched for Time calculations in Days & Hours. Thanks a lot.
Thank you very much, Tony.
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 😊
=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.
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.
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
Thanks alot for the teaching sir... It's very helpful
Great to hear. Thank you.
Thank you sir. You are best a best teacher I am very happy .may you live long.
Thank you Sandeep.
Thanks for detailed explanation can you please help to How to calculate average of the difference data generated?
You're welcome.
For that, we would need a column instead or in addition to this one, with the difference as a numeric value. We cannot average the results from this video as they are a text string.
A simple subtraction such as B2-A2 would achieve this, though if dates and times are in different columns, they need to be combined first.
Then you can average the results of the column.
is it possible to incorporate =IF(A2
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!
great video thanks for sharing it
Glad you enjoyed it. Thank you!
Thank you so much
You're most welcome
Thank You
You're very welcome.
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)
Excellent and very informative video!
Very Informative and Easy to Understand
Thank you, Iftiquar.
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?
Good job.
Thank you very much Daniel.
THANKS BUDDY, THIS ONE IS SAFE MY JOB😅😅😅
My pleasure!
Very important & useful lesson once again, thank you so much!
You're welcome Carlos. Thank you.
Thanks. Was very helpful!
Glad to hear it! Thank you.
so elegant sir
Brilliant! Thank you!!
My pleasure.
@Computergaga Thanks for sharing this. what will be the formula if I want to exclude the weekend? Please help..
Thank you so much for this. Really helpful
You're very welcome!
Help me a lot!
Awesome!
Very helpful , Thanks
You're welcome 👍👍
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.
Thanks for the tutorial..
You're welcome, Pradeep.
Wow you are amazing
Thank you 😊
hi, How to sum up days, hours and minutes?
Good job done you Sir, too much helpful you video sir........
Thanks and you're welcome, Hammad.
Great and easy way of explaining the
topic, thank you very much.
You're very welcome.
Thankyou, this helped me out!
Great to hear, Taione. Thank you.
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 - czcams.com/video/3bUVySH5prQ/video.html that explains this and more. It is a timesheet spreadsheet based on different work hours and days.
Thanks ur such a big help ☺️
You're welcome. Happy to help.
Awesome!!! you saved my day...
Awesome. Thank you Giri.
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.
This is really helpful
Great to hear 👍 Thanks.
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?
HI, is it possible to calculate the next date and time with counting 8 hrs per day.
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.
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.
Thank you dear but I have one more to clear, how to calculate the average of both difference day and time?
Thanks 👍
You're welcome.
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.
Ctrl+Shift+Alt+; Shortcut key works on that formula .
Thanks
You're welcome, Sajeer.
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 - czcams.com/video/_Fg4gl4qvss/video.html
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?
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.
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 ,?
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
great video thank you
Thank you Jay.
Well explained, thank you, keep up the good work!!
thanks a lot
You're very welcome, Vasilis.
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 🙏
Thanku sir ,its very help full for me .
Great to hear Ankita. I'm happy to help.
Thank You!!!!!
You're welcome Crystal.
Perfect!! Thank you.
You're welcome Vinesh.
Awesome
Thank you.
Once you get the Days, hours and minutes, is there any way to sun this elasped time? Thx
Hi, How do you Add in the ability to also measure Years and Months whilst still keeping all the information above? thanks
How to exclude Downtime or Non-working hours from two different dates & time while calculating Time Spent? Please help.
please upload some other advance videos on indirect function
No problem Jacky. Will have a video soon.
thank you sir ________
Excellent
Thank you Majid.
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)
Brilliant !!!
Thank you Zahin.
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.
How can you get average of the two to demonstrate day, hour and minutes
I need help. How can i exclude Weekends ?
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?
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)
If , i use different column like start date, start time, end date ,end time... What is the formula??
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?
👏🏻👏🏻👍🏻👍🏻 was so helpful
I'm so glad! Thank you, Jelveh.
I keep having problems with the mod function between the cells.Any help?
Nice information
Thanks Zaman.
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.
Not what i was looking for. but that being said. you can make this mutch easier with a custum format. just make a format ["D"] : [H]:mm:ss
How can I calculate the elapsed time in days hrs and mins as just minutes
What is formula if calculate as per (8hrs)as working hrs?
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 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😢
how do i exclude weekends from this?
Is it possible to get the average of the result?
What if the "end date" is blank or not filled, What will be the formula?
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.
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.....??
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.
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.
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 - czcams.com/video/OXw5X-HMwiU/video.html
Sir will it work with Excel 365
Yes, absolutely
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!!!
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
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.
Thank you so much.
My pleasure Natthawipha.
Using your formula for time elapsed in days, hours and minutes, how would you find the average time?
B2=Date 1=2020-07-03 22:59:55
C2=Date 2=2020-07-02 15:14:22
A2 =Text(B2-C2,"dd:hh:mm:ss")
=01:07:45:33
solution1:
DAY(LEFT(TEXT(B2-C2,"dd:hh:mm:ss"),2))+TIME(MID(TEXT(B2-C2,"dd:hh:mm:ss"),4,2),MID(TEXT(B2-C2,"dd:hh:mm:ss"),7,2),RIGHT(TEXT(B2-C2,"dd:hh:mm:ss"),2))
or Soln 2
:
DAY(LEFT(TEXT(A2,"dd:hh:mm:ss"),2))+TIME(MID(TEXT(A2,"dd:hh:mm:ss"),4,2),MID(TEXT(A2,"dd:hh:mm:ss"),7,2),RIGHT(TEXT(A2,"dd:hh:mm:ss"),2))
Result:
'=1143333 if format to [ss]
'=01:07:45:33 if format to dd:hh:mm:ss
'=31:45:33 if format to [h]:mm:ss
but whichever format is used in the result, u can apply average function now.