Hello,I need to update this formula =INT(AG1-AI1)&" Days "& INT (MOD(AG1-AI1,1)*24)&" Hours" to exclude Saturday and Sunday.

Please help.

✔ Best Answer

Here is an updated formula that will take into account if both dates are the same: =IF(DATEDIF(A1,B1,"D")=0,NETWORKDAYS(A1,B1)-1,NETWORKDAYS(A1,B1)-2)&" DAYS "&HOUR(MOD(B1-A1,1))&" HOUR "&MINUTE(MOD(B1-A1,1))&" MINUTES"

MIKE

message edited by mmcconaghy

See this thread: http://www.computing.net/answers/of...

This question should have been posted in the Office Software Forum, same place you originally posted your other questions about Excel.

MIKE

message edited by mmcconaghy

Robe, The moderator has deleted your other two posts in regards to this question.

If you post in the wrong forum, the moderator will usually move the thread to the appropriate forum, you do not have to post the same question twice.My comment was to remind you to post in the proper forum, and to alert the moderator that the thread should be moved.

If you have any further questions, please

REPLYto the post, do not create another one.As to your concerns about the formula displaying the First Day as number 1,

this was discussed in the referenced thread, beginning at response # 7.Does the suggested modified formula not work for you?

MIKE

My sincerest apologies for my mistake.

Yes the formula is not working based on what I need. As what I have posted here below is my scenario

I have visitid the link you have provided and tried =NETWORKDAYS(C91,AG91)-1&" DAYS "&HOUR(MOD(AG91-C91,1))&" HOUR "&MINUTE(MOD(AG91-C91,1))&" MINUTES"

Computing these two days for example: 5/16/2014 2:54:56 PM 5/19/2014 4:58:45 AM gives me a result of 1 DAYS 14 HOUR 3 MINUTES

However, it is less than 24 hours so it will not show 1 day.

I don't understand what you mean by "it is less than 24 hours so it will not show 1 day"Why would you want it to show 1 day when it is less than 24 hours? Seems to me that you would want to see

0 Days ## Hours.If it showed 1 day for time differences less than 24 hours, it would be wrong.

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

Computing these two days for example: 5/16/2014 2:54:56 PM 5/19/2014 4:58:45 AM gives me a result of 1 DAYS 14 HOUR 3 MINUTESBut looking at these two dates it is only 14 hours and not suppose to be 1 DAYS 14 HOUR 3 MINUTES

OK, I see what you mean. I do not have Excel available to me at the moment, will work on it later today.

MIKE

For the way you wish to display the data, I believe that simply subtracting 2 instead of 1 will display the data in the format you wish, so something like: =NETWORKDAYS(C91,AG91)-2&" DAYS "&HOUR(AG91-C91)&" HOUR "&MINUTE(AG91-C91)&" MINUTES"

The NETWORKDAYS function excludes weekends,

but it apparently counts whole days beginning with your start date, and stopping on your end date.

So NETWORKDAYS will always return at least 2, unless both dates are the same, in which case it returns 1.Also, since you wish to exclude Weekends, have you also considered Holidays?

MIKE

Here is an updated formula that will take into account if both dates are the same: =IF(DATEDIF(A1,B1,"D")=0,NETWORKDAYS(A1,B1)-1,NETWORKDAYS(A1,B1)-2)&" DAYS "&HOUR(MOD(B1-A1,1))&" HOUR "&MINUTE(MOD(B1-A1,1))&" MINUTES"

MIKE

message edited by mmcconaghy

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History