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?

✔ Best Answer

Something like this should work: =NETWORKDAYS(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

MIKE

See how you like this: =INT(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

MIKE

Just noticed you wanted a decimal format,

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

MIKE

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?

Something like this should work: =NETWORKDAYS(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

MIKE

Thank you, Mike, I appreciate your help! This worked beautifully.

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

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!

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.

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

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

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History