Excel formula for calculating number of room nights

Hi! I am running a guest house and need to calculate the number of room nights. The check in and check out times are at noon. If some one checks in 1 hr before noon or checks out one hour after noon, each should count as one night extra.

A1 B1 C1
27 May 2015 01:35 29 May 2015 18:20 3

For example if I put above values in cells A1 and B1, then C1 should give me 4 nights (by using the formula ROUND(B1,0)-INT(A1) ). But it is giving 3 nights. How can I modify this formula? Pls help.

Ok let me see if that works..Thanks a lot..will revert

It works in this particular case but if i use the same formula for a normal noon to noon stay then it is adding one day extra!

Thats perfect! tHANKS

I meant Thanks nflsales. The formula works now. I will observe for a few bookings and let you know if any problems.

nflsales formula didn't work for me, according to your description. Noon of one day to noon of the next day is technically 2 nights... by your description (I have no idea why others are using 11AM and 1PM, because it only results in one day).

=INT(B1+12/24)-INT(A1-12/24) works in a technical fashion. Zbor was on the same track but instead of adding for the checkout time, he padded with the 1+, which gives the wrong result in some instances. If you want to pad the result for slightly early checkin or slightly late checkout the 12 in my formula represent hour of the day, as in Zbor's formula.

Hi jhren, in our system we charge once for noon to noon. So the nfsales is working fine for me. Anything before the checkin noon (For eg 0700 hrs to 1200 hrs) is charged as one day. Similarly anything after checkout noon (For eg 1500 hrs) is charged as an extra day.

I guess I should use the words No of Charges instead of number of room nights.

If that's the case, then =INT(B1+12/24)-INT(A1-7/24) would not charge the guest an extra night for checking in at 7-11:59AM... while it would for a guest for checking in at 6:59 or earlier.

I have another question. I have a column say of 10 cells say A1 to J1. I want a sum of only those cells which are showing a number value. I want to exclude the cells which are showing ####.

Pls advise which formula to use?

Need two cells, for example B1 and B2. In B1 enter the number you want to exclude. In B2...

Formula:

Please Login or Register  to view this content.

PS: Earlier I stated, "If you want to pad the result for slightly early checkin or slightly late checkout the 12 in my formula represent hour of the day..." To be more precise, it represent hours until next day for checkout time, and hours from previous day for check in time.

Can someone please check my formula =SUM(IF(E2>=$B$2:$B$239, IF(E2<$C$2:$C$393, 1, 0), 0)) , I want to count the number of nights per date throughout the column.

Sample:

Column B Column C Column E
Arrival Departure Date Allotment formula
25-Jan-19 27-Jan-19 24-Jan-19 =SUM(IF(E2>=$B$2:$B$239, IF(E2<$C$2:$C$393, 1, 0), 0))
27-Jan-19 29-Jan-19 25-Jan-19 =SUM(IF(E3>=$B$2:$B$239, IF(E3<$C$2:$C$393, 1, 0), 0))

see above post