Solved Excel formula Exclude Saturday and Sunday

June 22, 2014 at 18:39:35
Specs: Windows XP

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.


See More: Excel formula Exclude Saturday and Sunday

Report •


✔ Best Answer
June 24, 2014 at 08:22:52
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

http://www.skeptic.com/

message edited by mmcconaghy



#1
June 22, 2014 at 18:53:53
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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
June 23, 2014 at 07:45:37
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 REPLY to 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

http://www.skeptic.com/


Report •

#3
June 23, 2014 at 19:12:12
My sincerest apologies for my mistake.

Report •

Related Solutions

#4
June 23, 2014 at 19:14:17
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.


Report •

#5
June 23, 2014 at 20:15:05
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.


Report •

#6
June 23, 2014 at 20:20:23
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

But looking at these two dates it is only 14 hours and not suppose to be 1 DAYS 14 HOUR 3 MINUTES


Report •

#7
June 24, 2014 at 05:03:44
OK, I see what you mean.

I do not have Excel available to me at the moment, will work on it later today.

MIKE

http://www.skeptic.com/


Report •

#8
June 24, 2014 at 07:15:30
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

http://www.skeptic.com/


Report •

#9
June 24, 2014 at 08:22:52
✔ 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •


Ask Question