Solved Question on the formula

July 28, 2016 at 13:54:52
Specs: Windows 7
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


See More: Question on the formula

Report •


✔ Best Answer
July 28, 2016 at 15:35:01
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

http://www.skeptic.com/



#1
July 28, 2016 at 14:09:48
"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?

Always pop back and let us know the outcome - thanks


Report •

#2
July 28, 2016 at 14:35:13
NETWORKDAYS() function returns the number of whole working days between 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.


Derek

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


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#3
July 28, 2016 at 15:28:41
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"

Report •

Related Solutions

#4
July 28, 2016 at 15:35:01
✔ 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

http://www.skeptic.com/


Report •

#5
July 28, 2016 at 15:36:30
ArjunBadhan

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

message edited by Derek


Report •

#6
July 28, 2016 at 16:28:18
thanks guys!! and thanks for clarifying Mick!!

Report •

Ask Question