Calculating Weeks, Weekday nights and Weekend nights

**bngguy** · 10-21-2010, 02:25 PM

Originally Posted by DonkeyOte

From what I can tell the Demo works, no ?

(NETWORKDAYS.INTL is exclusive to XL2010 and beyond)

Ok but you get an idea of what i'm trying to do, i have a check-in date and check-out date, using these two dates i need to find out the number of weekday nights(SUN-THU) and number of weekend Nights(FRI-SAT).

**DonkeyOte** · 10-21-2010, 02:27 PM

Perhaps you could explain how the sample you posted isn't doing what you want because to me it seems to do exactly as you requested.

**bngguy** · 10-21-2010, 02:43 PM

Originally Posted by DonkeyOte

Perhaps you could explain how the sample you posted isn't doing what you want because to me it seems to do exactly as you requested.

Lets say somebody checks in on Sunday morning and checks out on Friday Morning,the number of weekday nights should be 5 and weekend nights should be zero.
With the formula i used i get number of weekday nights to be 4 and number of weekend nights to be 1.

**DonkeyOte** · 10-21-2010, 02:46 PM

Ah, sorry - yes I see !

=MAX(0,NETWORKDAYS.INTL(B2,C2-1,7))

the above discounts check out date - the MAX should prove superfluous but you never know...

**bngguy** · 10-21-2010, 03:30 PM

the above discounts check out date - the MAX should prove superfluous but you never know...[/QUOTE]

Thanks that works well, i have another question though, is it possible to set check in and check out dates to Today() as default.

**bngguy** · 10-25-2010, 07:08 PM

Thanks that works well, i have to add another column that computes the number of weeks between 2 dates.Please See Demo.

**DonkeyOte** · 10-26-2010, 02:27 AM

No need to quote prior posts in their entirety in your reply - this just clutters the board.

I confess that again I am a little confused by the example

Check-In 7-Oct
Check-Out 28-Oct

is 21 nights so why is the expected results anything other than 3 weeks ?

Were

Check-Out 27-Oct

then I concur with 2 weeks, 4 weekday and 2 weekend:

D2:
=MAX(0,NETWORKDAYS.INTL(B2+(F2*7),C2-1,7))

E2:
=C2-B2-(F2*7)-D2

F2:
=MAX(0,INT(C2-B2/7))

**bngguy** · 10-26-2010, 03:19 AM

Thanks i had already solved that one before you posted,now my last task is to have a message popup whenever the stay is exceeded by 3 weeks.I would really appreciate if you could help me out with this one:

If ((F2>3) OR ((F2 = 3) AND ((E2 >1) OR (D2>1)))) then
MsgBox (" Stay Cannot Exceed 3 Weeks !!! ")
End if

Except F2,E2,D2 are all columns with 54 rows, so when the user enters the check-in date and check-out date, D2 , E2 , F2 are computed and the macro should immediately alert the user if the above conditions are met.I'm really new to VBA, if you could code the macro for me that would be great.

Thanks