Solved Excel - Need to calculate days and hours between two dates

Microsoft Excel 010 - complete package
March 28, 2013 at 08:34:01
Specs: Windows XP
I have two time stamps formatted like this: 3/24/13 12:30 PM and 3/28/13 2:00 PM and I need Excel to come back with 4 days 1.5 hours. So far, I can get it to say days OR hours but I can NOT get it to do both. Right now I'm using datedIF(A3,B3,"d") because it looks better than the other things I've found. Can anyone help?


See More: Excel - Need to calculate days and hours between two dates

Report •


✔ Best Answer
April 1, 2013 at 13:13:44
Something like this should work:

=NETWORKDAYS(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

MIKE

http://www.skeptic.com/



#1
March 28, 2013 at 13:41:50
See how you like this:

=INT(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

MIKE

http://www.skeptic.com/


Report •

#2
March 28, 2013 at 13:48:13
Just noticed you wanted a decimal format,
try this:

=INT(B3-A3)&" Days "&MOD(B3-A3,1)*24&" Hours"

MIKE

http://www.skeptic.com/


Report •

#3
April 1, 2013 at 05:34:38
Thank you so much, Mike, I actually like the first one best that shows the minutes too. I see something called Networkdays that will take out the weekends. Is there anyway to incorporate that into this formula?

Report •

Related Solutions

#4
April 1, 2013 at 13:13:44
✔ Best Answer
Something like this should work:

=NETWORKDAYS(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

MIKE

http://www.skeptic.com/


Report •

#5
April 2, 2013 at 05:42:53
Thank you, Mike, I appreciate your help! This worked beautifully.

Report •

#6
April 2, 2013 at 13:02:56
Since your using =NETWORKDAYS there is also a way to exclude Holidays.

Find a column that is out of the way, like column AA

List your Holiday Dates down column AA like:

        AA
1) 12/25/2013
2) 01/01/2013
3) 02/18/2013
4) 05/21/3013
5) 07/04/2013
etc. etc.

Once you have your complete list of Holidays, add to the formula like:

=NETWORKDAYS(A3,B3,$AA$1:$AA$5)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

MIKE

http://www.skeptic.com/


Report •

#7
April 3, 2013 at 05:22:10
Well, the boss didn't like networkdays because it kept showing the first workday so if something came in on 4/1 and left on 4/1, it still said one day. If it came in on 4/1 and left on 4/2, it was two days so he had me go back to your first suggestion and INT. To keep it from formulating an answer when both dates weren't entered, the final forumla looks like this: IF(B3<1,"",INT(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes")

This is working for us though I hate the weekends there!


Report •

#8
April 3, 2013 at 06:42:33
I haven't been following this thread too closely, but if the NETWORKDAYS function is returning 1 too many days, why not just subtract one from it?

=NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Days "&
HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

For same day entries, the formula above returned this:

0 Days 2 Hour 52 Minutes

If you don't want the "0 Days" displayed, try this:

=IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1=0,HOUR(MOD(B3-A3,1))&
" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",
NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Days "&HOUR(MOD(B3-A3,1))&
" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes")

To get this:

2 Hour 52 Minutes

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
April 3, 2013 at 06:50:00
How about this then:

=NETWORKDAYS((A3,B3,$AA$1:$AA$5)-1)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

Simply subtract 1 from the =NETWORKDAYS() function.

It will then show 0 Days XX Hours XX Minutes.

MIKE

http://www.skeptic.com/


Report •

#10
April 3, 2013 at 14:04:57
If you want to check to make sure both date cells are filled, then something like this:

I've used DerbyDad03's version of the formula, that won't show the "0 Days"

=IF(OR(A3="",B3=""),"",IF(NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1=0,HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes",NETWORKDAYS(A3,B3,$AA$1:$AA$5)-1&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"))

It is very long, so you might want to simply Copy&Paste.

MIKE

http://www.skeptic.com/


Report •


Ask Question