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.
@@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) :)
How to use the same formula and avoid the holidays, because i am planning to calculate IRT and ORT using excel in my CM project. Please advise and thank you in advanced sir
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.
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?
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, 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 - ruclips.net/video/3bUVySH5prQ/видео.html that explains this and more. It is a timesheet spreadsheet based on different work hours and days.
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.
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.
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 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?
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 ,?
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?
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?
I know the video is 7 years old, but for whomever watching this in 2024, you can subtract ending value feom beginning value and format the cell using cutsom format. And in (type) section write: dd "Days" : hh "Hours" : mm "Minutes" as an easier an quicker way to get the same result.
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 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?
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)
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😢
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.
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
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 - ruclips.net/video/OXw5X-HMwiU/видео.html
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.....??
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.
@@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.
Hi Kiran, you would format the total cell using [h] to get total time over 24 hours. I have a video on a similar topic here - ruclips.net/video/_NMXXuNMy7A/видео.html
is it possible to incorporate =IF(A2
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 😊
This is the best video I have watched for Time calculations in Days & Hours. Thanks a lot.
Thank you very much, Tony.
@Computergaga Thanks for sharing this. what will be the formula if I want to exclude the weekend? Please help..
=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.
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?
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!
How to use the same formula and avoid the holidays, because i am planning to calculate IRT and ORT using excel in my CM project. Please advise and thank you in advanced sir
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)
HI, is it possible to calculate the next date and time with counting 8 hrs per day.
I never comment on videos but this was just great and helped me resolve my issue. Thanks 🙏
That's awesome. Thank you, Akhmad.
hi, How to sum up days, hours and minutes?
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
Excellent and very informative video!
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.
Good Job Explaining. Finally got it after adjusting the cell formatting!
Thank you! Glad that it helped.
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 dear but I have one more to clear, how to calculate the average of both difference day and time?
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.
Once you get the Days, hours and minutes, is there any way to sun this elasped time? Thx
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, 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 - ruclips.net/video/3bUVySH5prQ/видео.html that explains this and more. It is a timesheet spreadsheet based on different work hours and days.
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.
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 alot for the teaching sir... It's very helpful
Great to hear. Thank you.
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 - ruclips.net/video/_Fg4gl4qvss/видео.html
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.
Very Informative and Easy to Understand
Thank you, Iftiquar.
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.
Hi, How do you Add in the ability to also measure Years and Months whilst still keeping all the information above? thanks
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 ,?
I keep having problems with the mod function between the cells.Any help?
Very important & useful lesson once again, thank you so much!
You're welcome Carlos. Thank you.
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.
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?
I know the video is 7 years old, but for whomever watching this in 2024, you can subtract ending value feom beginning value and format the cell using cutsom format. And in (type) section write: dd "Days" : hh "Hours" : mm "Minutes" as an easier an quicker way to get the same result.
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)
I need help. How can i exclude Weekends ?
How to exclude Downtime or Non-working hours from two different dates & time while calculating Time Spent? Please help.
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?
If , i use different column like start date, start time, end date ,end time... What is the formula??
Very helpful , Thanks
You're welcome 👍👍
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.
Brilliant! Thank you!!
My pleasure.
How can you get average of the two to demonstrate day, hour and 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?
Thank you so much for this. Really helpful
You're very welcome!
Very helpful, thank you!
Glad it was helpful!
Ctrl+Shift+Alt+; Shortcut key works on that formula .
great video thanks for sharing it
Glad you enjoyed it. Thank you!
Thanks for the tutorial..
You're welcome, Pradeep.
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)
What is formula if calculate as per (8hrs)as working hrs?
Great and easy way of explaining the
topic, thank you very much.
You're very welcome.
How can I calculate the elapsed time in days hrs and mins as just minutes
Good job done you Sir, too much helpful you video sir........
Thanks and you're welcome, Hammad.
Thankyou, this helped me out!
Great to hear, Taione. Thank you.
THANKS BUDDY, THIS ONE IS SAFE MY JOB😅😅😅
My pleasure!
This is really helpful
Great to hear 👍 Thanks.
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 🙏
Is it possible to get the average of the result?
Awesome!!! you saved my day...
Awesome. Thank you Giri.
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
Well explained, thank you, keep up the good work!!
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😢
Well explained! Thanks - exactly what I needed!!
Thank you, Maria.
Thanks ur such a big help ☺️
You're welcome. Happy to help.
how do i exclude weekends from this?
so elegant sir
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.
What if the "end date" is blank or not filled, What will be the formula?
Good job.
Thank you very much Daniel.
please upload some other advance videos on indirect function
No problem Jacky. Will have a video soon.
thank you sir ________
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
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.
Thank you so much
You're most welcome
great video thank you
Thank you Jay.
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 - ruclips.net/video/OXw5X-HMwiU/видео.html
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.
👏🏻👏🏻👍🏻👍🏻 was so helpful
I'm so glad! Thank you, Jelveh.
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.....??
Wow you are amazing
Thank you 😊
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.
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!!!
Sir will it work with Excel 365
Yes, absolutely
Thanku sir ,its very help full for me .
Great to hear Ankita. I'm happy to help.
Thank You
Thanks 👍
You're welcome.
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.
Nice information
Thanks Zaman.
Help me a lot!
Awesome!
now how r we supposed to plot it?
Sir according to first example in this video how can I calculate total time, for example how time displayed as 71 hours 50 minutes
Please reply
Hi Kiran, you would format the total cell using [h] to get total time over 24 hours. I have a video on a similar topic here - ruclips.net/video/_NMXXuNMy7A/видео.html
After getting days and hour how can we less 6 hours in this formula?
Brilliant !!!
Thank you Zahin.
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
Perfect! Thank you.
My pleasure Todd.
Thank You!!!!!
You're welcome Crystal.
Excellent
Thank you Majid.
Thanks
You're welcome, Sajeer.