Calculating Weeks, Weekday nights and Weekend nights

[bngguy]

Hello,
I'm developing a register for a hostel, given two set of dates (check-in and check-out)
i have to calculate the number of weekday nights(Sun-Thu) and weekend Nights (Fri-Sat),i'm really new to excel and i would appreciate any help in doing this.

Thanks in advance,
Av

[zbor]

Please, upload an example workbook with desired inpt and output.

[bngguy]

Please, upload an example workbook with desired inpt and output.

I have uploaded a demo, please let me know if this is right.

[DonkeyOte]

From what I can tell the Demo works, no ?

(NETWORKDAYS.INTL is exclusive to XL2010 and beyond)

[bngguy]

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]

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]

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]

Ah, sorry - yes I see !

Please Login or Register  to view this content.

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

[bngguy]

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]

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

[DonkeyOte]

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:

Please Login or Register  to view this content.

[bngguy]

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

[DonkeyOte]

Thanks i had already solved that one before you posted,

In which case please have the courtesy to post to that effect rather than have people waste their freely donated time resolving a now obsolete problem.

Regards your three week requirement - in the context of the thread this is an entirely new requirement and as such should be posted to a new dedicated thread.

[bngguy]

I'm sorry if i offended you, i will post a new thread for the 3 week requirement.

[bngguy]

Hello,
I'm developing a register for a hostel, given two set of dates (check-in and check-out), i need 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:

Please See DEMO File.

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
AV

[DonkeyOte]

@AV, as already mentioned this latest question belongs in an entirely new thread

edit: I see you have done this: http://www.excelforum.com/excel-prog...age-popup.html