Excel Magic Trick 299: Date & Time Number - Total Days & Hours Formula

how to calculate it when on date and time was already used concatnate funkcion

13 years old. I can hardly believe it. This quicky still shines like the sun on a hot summer Sunday afternoon. A very important topic, actually. Thanks for these focused tricks.

I had duration in days in one spreadsheet and I copied it to another spreadsheet but it turned to dates and time. How do I convert back to days?

Wow! Thank you so much for this.

Hi

Hi.
I really need help for this point
for example i have this results
2Years, 4Months,3Days
and i want to sum it together Like
2Years,4Months,3Days+1Years,2Months,2Days.=...?
how to do this ?
Thanks.

Thanks!

Thanks you sir, but say how about if i wanted to include the minutes?

Nice

is helpful video. worked

Sir, Is it possible to write date and time function in a single cell ?

How to ignore weekends sat and Sunday if any in between, while calculation ?

Thank you sir. Don't stop teaching. God bless always.

It really is magic. I love it!! It works!!

none of it works if you can't figure out how to enter a 'square-bracket"..

EXCELlent Mike.

how to calculate the half of an hour

I seem to be getting weird results with this,are you able to help 07/01/2013 10:41:56 - 07/02/2013 06:29:16 = 31 days 739 hours format is mm/dd/yyyy hh:mm:ss

Best to do with VBA. I am not good with VBA. Try THE best Excel site (many good VBA people): mrexcel [dot] com/forum

Mr. Girvin, you make it look so easy! Anyways, have a question. I want to split say, total number of days elapsed between two dates and list them as "1 month 16 days" for 46 days elapsed. Also, format the data so appropriate singular or plural notation is in effect depending on 1 or more days or months. Thus 109 days would possibly translate into 3 months and 18 days or 11 days would simply be that, 11 days; or exact 2 months (no days) etc. How to accomplish that? Thanks in advance.

I have book and DVD: mrexcel [dot] com/slayingdragonsbundle.html

Thanks again, very useful video and it solved yet another how to question regarding date/time calculations between dates and times. Do you have your videos on CD or USB we can order? It would save me some time downloading you tube videos.

Shouldn't it be 21 days 4 hours... try: =INT(C13)-INT(C12)&" days and "&TEXT(C13-C12,"hh:mm:ss")&" hours"

As I analyze the trick, the Days & Hours should have been 20 days & 4:00:33hours. I try this function =INT((TEXT(C13-C12,"[h]")+0)/24)&" days & "&TEXT(TEXT(C13-C12,"[h]")-INT((TEXT(C13-C12,"[h]"))),"[h]:mm:ss")&" hours" but it came out like this 20 days & 0:00:00hours. HOPE YOU CAN HELP ME OUT. THANKS A LOT

This might work for a Time Number formatting: [h]:mm

You are welcome!

I'll keep making them! Excel fun is addictive and helpful at work too!

Hi there. I am a total excel addict and I am always looking for something new to learn. Great vids and keep up the good work.

Dear loverpeace33, I am glad that they videos are helpful! I'll keep making them! --excelisfun

Dear chirag1883, I am glad you like them! --excelisfun

Nice trick. Thanks