Hi.. The original formula : =NETWORKDAYS(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes" works fine to calculate the difference between 2 dates in excel.

However, I have a question here that why are we using MOD function here because HOUR and MINUTE functions itself can return the desired values of Hour and Minute from the expression B3-A3 as they both know that Hour and Minute are saved in the integer part of the expression.

message edited by ArjunBadhan

✔ Best Answer

It should work.

I never thought to use just HOUR() and MINUTE() functions without first getting the time portion, very good observation.

I shall add it to my bag of tricks.

Thanks.MIKE

" why are we using MOD function"

Is this query about a question already asked on here? If so can we have a link to it please?

Alwayspop back and let us know the outcome - thanks

NETWORKDAYS() function returns the number of whole working daysbetween two dates excluding specified holidays, and it knows nothing about TIME

Hour and Minute are saved in the integer part of the expression.No, The integer portion of the number, represents the number of days since 1900-Jan-0.

The Time is the fractional portion of the number, where it represents the fractional portion of a 24 hour day.So when using NETWORKDAYS() you need to manually separated out the time to do the calculations.

DerekThis is an Excel formula and it should have been posted in the Office Software forum.

MIKE

message edited by mmcconaghy

Hi Derek, itâ€™s from an old question asked on MS Excel forum, where Mike replied to the question. Here is the link : http://www.computing.net/answers/of...

Mike, the formula works fine and I understand the use of NETWORKDAYS() here. I was just wondering that can we use this formula without using MOD() function here.

This formula : =NETWORKDAYS(A3,B3)&" Days, "&HOUR(B3-A3)&" Hours, "&MINUTE(B3-A3)&" Minutes"

instead of this formula : =NETWORKDAYS(A3,B3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"

It should work.

I never thought to use just HOUR() and MINUTE() functions without first getting the time portion, very good observation.

I shall add it to my bag of tricks.

Thanks.MIKE

ArjunBadhan Thanks for taking the trouble to clarify that. You're in good hands with mmcconaghy

message edited by Derek

thanks guys!! and thanks for clarifying Mick!!

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History