Well explained, thank you, keep up the good work!!

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?

Very important & useful lesson once again, thank you so much!

@Computergaga

6 жыл бұрын

You're welcome Carlos. Thank you.

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"

@nawazshaikh6081

3 жыл бұрын

what will be the formula if I want to exclude the weekend? Please help..

@Computergaga

2 жыл бұрын

You would use NETWORKDAYS or NETWORKDAYS.INTL instead of B2-A2.

@rahultomar8546

2 жыл бұрын

@@Computergaga Hello Sir please type Full formula with exclude weekend and I want result in days Hour minutes

@francenewhyte6016

2 жыл бұрын

Good Day. How can I show days only. So for example I only want to reflect full days.

@j.rjunior5584

Жыл бұрын

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.

Great and easy way of explaining the topic, thank you very much.

@Computergaga

4 жыл бұрын

You're very welcome.

Well explained! Thanks - exactly what I needed!!

@Computergaga

2 жыл бұрын

Thank you, Maria.

This is the best video I have watched for Time calculations in Days & Hours. Thanks a lot.

@Computergaga

2 жыл бұрын

Thank you very much, Tony.

@Computergaga Thanks for sharing this. what will be the formula if I want to exclude the weekend? Please help..

Hi, How do you Add in the ability to also measure Years and Months whilst still keeping all the information above? thanks

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?

@Computergaga

5 жыл бұрын

Something I have not tried. But I am sure the equivalent is possible there. Lots to explore in DAX.

Thank You

is it possible to incorporate =IF(A2

Brilliant! Thank you!!

@Computergaga

5 жыл бұрын

My pleasure.

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

Good Job Explaining. Finally got it after adjusting the cell formatting!

@Computergaga

11 ай бұрын

Thank you! Glad that it helped.

so elegant sir

Perfect!! Thank you.

@Computergaga

4 жыл бұрын

You're welcome Vinesh.

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?

@Computergaga

2 жыл бұрын

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.

Sir your education is like a burning lamp. Who shows the right path. We are very happy with you.

@Computergaga

Жыл бұрын

That is beautiful, Sandeep. Thank you 😊

Excellent and very informative video!

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

@Computergaga

5 жыл бұрын

Thank you Huma. I have a video here - kzread.info/dash/bejne/ZZaJuNuMeJjWorQ.html that explains this and more. It is a timesheet spreadsheet based on different work hours and days.

Thanks you for your very informative video! But I do have a question, how would you add all of your differences together afterwards?

@MuddyBuddy685

3 жыл бұрын

Did you ever figure out how to add them all up?

@erimwolf7285

3 жыл бұрын

@@MuddyBuddy685 unfortunately, no

@MuddyBuddy685

3 жыл бұрын

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

@erimwolf7285

3 жыл бұрын

​@@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) :)

@MuddyBuddy685

3 жыл бұрын

@@erimwolf7285 Great! Thank you so much!

hi, How to sum up days, hours and minutes?

I never comment on videos but this was just great and helped me resolve my issue. Thanks 🙏

@Computergaga

Жыл бұрын

That's awesome. Thank you, Akhmad.

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.

@Computergaga

2 жыл бұрын

Thank you, Karishma. NETWORKDAYS would replace the B2-A2 calculation.

@ChopsB8X

2 жыл бұрын

@@Computergaga but how? Can you show the formula based on your video? Thank you 🙏

Awesome!!! you saved my day...

@Computergaga

5 жыл бұрын

Awesome. Thank you Giri.

Very Informative and Easy to Understand

@Computergaga

4 жыл бұрын

Thank you, Iftiquar.

HI, is it possible to calculate the next date and time with counting 8 hrs per day.

Thanks alot for the teaching sir... It's very helpful

@Computergaga

2 жыл бұрын

Great to hear. Thank you.

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)

Once you get the Days, hours and minutes, is there any way to sun this elasped time? Thx

Hi this is really useful. What changes would I have to make to the formula to display the time elapsed in hours?

@Computergaga

4 жыл бұрын

Thank you, Marc. I have another video on techniques for this here - kzread.info/dash/bejne/kXqblsmlZNTco9Y.html

I keep having problems with the mod function between the cells.Any help?

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?

@Computergaga

3 жыл бұрын

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.

@dwk1986

3 жыл бұрын

@@Computergaga can you actuall show how to do this? as well as showing how to average these types of datas?

Very helpful, thank you!

@Computergaga

2 жыл бұрын

Glad it was helpful!

What is formula if calculate as per (8hrs)as working hrs?

Thanku sir ,its very help full for me .

@Computergaga

5 жыл бұрын

Great to hear Ankita. I'm happy to help.

Thank You!!!!!

@Computergaga

6 жыл бұрын

You're welcome Crystal.

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.

@Computergaga

4 жыл бұрын

Thank you Dhaval for your kind words.

@dhavalmistry9901

4 жыл бұрын

@@Computergaga Mostly well come Sir. Have good day

Brilliant !!!

@Computergaga

4 жыл бұрын

Thank you Zahin.

Thank you so much for this. Really helpful

@Computergaga

Жыл бұрын

You're very welcome!

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?

great video thank you

@Computergaga

4 жыл бұрын

Thank you Jay.

Thankyou, this helped me out!

@Computergaga

3 жыл бұрын

Great to hear, Taione. Thank you.

Thanks. Was very helpful!

@Computergaga

9 ай бұрын

Glad to hear it! Thank you.

If , i use different column like start date, start time, end date ,end time... What is the formula??

Thanks for the tutorial..

@Computergaga

3 жыл бұрын

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?

@Computergaga

2 жыл бұрын

Sure. You can enter that in a cell.

Thanks for detailed explanation can you please help to How to calculate average of the difference data generated?

@Computergaga

Күн бұрын

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.

Thank you sir. You are best a best teacher I am very happy .may you live long.

@Computergaga

Жыл бұрын

Thank you Sandeep.

now how r we supposed to plot it?

Nice information

@Computergaga

5 жыл бұрын

Thanks Zaman.

Perfect! Thank you.

@Computergaga

5 жыл бұрын

My pleasure Todd.

Thanks for a great video! What do you mean in your other comments to swap b2-a2 with networkdays to remove weekends. So confused! 🥺

@Computergaga

2 жыл бұрын

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 really helpful

@Computergaga

3 жыл бұрын

Great to hear 👍 Thanks.

👏🏻👏🏻👍🏻👍🏻 was so helpful

@Computergaga

3 жыл бұрын

I'm so glad! Thank you, Jelveh.

Good job done you Sir, too much helpful you video sir........

@Computergaga

2 жыл бұрын

Thanks and you're welcome, Hammad.

Is it possible to get the average of the result?

Very helpful , Thanks

@Computergaga

Жыл бұрын

You're welcome 👍👍

Excellent

@Computergaga

5 жыл бұрын

Thank you Majid.

great video thanks for sharing it

@Computergaga

2 күн бұрын

Glad you enjoyed it. Thank you!

Hi, Great video....is there an easy way to show days hrs and mins from the calculation in separate cells?

@Computergaga

Жыл бұрын

Sure. Instead of combining the results, you can write separate formulas for each cell/column.

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?

Thank you dear but I have one more to clear, how to calculate the average of both difference day and time?

How do I get the negatives to appear red. I'm not sure how to build the custom formatting

@Computergaga

6 жыл бұрын

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 - kzread.info/dash/bejne/gYyrl7pmeLDdmbg.html

How to exclude Downtime or Non-working hours from two different dates & time while calculating Time Spent? Please help.

Thanks ur such a big help ☺️

@Computergaga

2 жыл бұрын

You're welcome. Happy to help.

Thank you so much

@Computergaga

3 ай бұрын

You're most welcome

thanks a lot

@Computergaga

4 жыл бұрын

You're very welcome, Vasilis.

How would you be able to get the average duration for multiple entries using your format?

@Computergaga

4 жыл бұрын

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.

Awesome

@Computergaga

5 жыл бұрын

Thank you.

Thanks 👍

@Computergaga

2 жыл бұрын

You're welcome.

Thank you

@Computergaga

9 ай бұрын

Welcome!

How can I calculate the elapsed time in days hrs and mins as just minutes

Good job.

@Computergaga

6 ай бұрын

Thank you very much Daniel.

how do i exclude weekends from this?

please upload some other advance videos on indirect function

@Computergaga

7 жыл бұрын

No problem Jacky. Will have a video soon.

@jackychawla5422

7 жыл бұрын

thank you sir ________

How can you get average of the two to demonstrate day, hour and minutes

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)

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?

@Computergaga

11 ай бұрын

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 ,?

Help me a lot!

@Computergaga

11 ай бұрын

Awesome!

Thanks

@Computergaga

3 жыл бұрын

You're welcome, Sajeer.

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 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

@Computergaga

5 жыл бұрын

It should be fine. If 10:20 was in cell A1 and 10:00 was in cell A2 then = A1-A2 is 00:20

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?

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.

@Computergaga

6 жыл бұрын

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.

@carlosalfonso7504

6 жыл бұрын

Thank you so much, that will work great! Thanks for all your lessons, they make a huge difference in the way I use Excel!!!

Can you do this with 4 date and time entries instead of 2?

@Computergaga

4 жыл бұрын

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.

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

@Computergaga

4 жыл бұрын

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.

Wow you are amazing

@Computergaga

7 ай бұрын

Thank you 😊

Is there a way to total the difference column?

@Computergaga

4 жыл бұрын

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.

works brilliantly but is there a way to have the result update automatically? thanks

@Computergaga

2 жыл бұрын

Great! But the closest you can get to automatic is some VBA code to run on intervals.

What format should you use for the dates and times?

@Computergaga

4 жыл бұрын

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.

Ctrl+Shift+Alt+; Shortcut key works on that formula .

great work

@Computergaga

5 жыл бұрын

Thank you Ibrahim.

@ibrahimsbarry4766

5 жыл бұрын

@@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

5 жыл бұрын

Did the video not work for you? What trouble are you having?

@ibrahimsbarry4766

5 жыл бұрын

@@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

@Computergaga

5 жыл бұрын

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.

THANKS BUDDY, THIS ONE IS SAFE MY JOB😅😅😅

@Computergaga

2 ай бұрын

My pleasure!

Does not work for me. Most probably not the correct data format for the date & time cells. What do you suggest? Thanks

@Computergaga

4 жыл бұрын

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.

@MatteoPozzoli

4 жыл бұрын

@@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

Sir will it work with Excel 365

@Computergaga

7 ай бұрын

Yes, absolutely

What if the "end date" is blank or not filled, What will be the formula?

Why would my result differ with exactly the same values. (88 Days23 Hours 50 minutes )

@paultaylor2522

2 жыл бұрын

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