13 Replies Latest reply: May 9, 2013 10:37 AM by Caleb Kingston
Caleb Kingston Level 1 Level 1 (130 points)

Hello there. I have a spreadsheet that shows check in, check out times for guests in 5 units: unit 1, 2, 3, 4, & 7 (5 and 6 aren't being rented).


I would like to have a chart that automatically displays the occupancy rate for each room as a guest is entered into the table. I'm not great at all with charts in numbers and another difficult part is creating formulars that detects the changes of a new month. So I've drawn a rough draft of what I'd like the chart to look like. Can anyone help me come up with a formula to make the chart display this?



  • jaxjason Level 4 Level 4 (3,460 points)

    I would really like to help with this but might need some more info. first if you could clarify for those of us not in the rental or hotel business, how is "occupancy rate" calcuated by hand?


    Second, if you could actaully hide the columns in your screen shot instead of blurring them out. Blurring them just seems to makes it harder to see when it shows in the limited zoom window.


    Offhand, if % occupancy is as simple as (number of days occupied)/30 then you can have a summary table that has date range versus Cabin number. Use sumifs to add up your column of days per cabin. divide by the date difference between start and end dates. plot that.



  • Caleb Kingston Level 1 Level 1 (130 points)

    Thank you, Jason,


    You are correct. It is the [number of days occupied]/[total days of the month]. The problem I would have is detecting when one month starts and another ends because some guests have booking dates like 12/28/12 to 1/3/13 so some are in december and some are in January.


    I'm very sorry I'm a total noob when it comes to formulas and I'm not even sure where to start. I might need a class just on creating formulas for tables. lol


    Here is a link with the table that shows the check-in check-out as well as the total days for each guest. If you could help me out, I'd be tremendously grateful!







    Screen Shot 2013-05-07 at 2.59.16 PM.png

  • Jerrold Green1 Level 7 Level 7 (29,925 points)



    Thanks for making your data available. The first thing I noticed is that you have some data entry errors in the dates. It isn't apparent when you use the short year form, but some of your dates are in the years 0012 and 0013, 2000 years ago. Sorting that out will improve the calculations.



  • Jerrold Green1 Level 7 Level 7 (29,925 points)



    Here's a simple calculation, not exactly what you wanted, but much easier to achieve.


    Screen Shot 2013-05-07 at 5.50.22 pm.png


    In the summary table I used a SUMIF function to find the total days booked for each unit, and for the total days available, I subtracted the earliest check-in date from the latest check-out date.



  • Caleb Kingston Level 1 Level 1 (130 points)

    Good catch, Jerrold, thanks!


    I've revised the new table here



  • Caleb Kingston Level 1 Level 1 (130 points)

    This might be exactly what I need, Jerrold. Let me try it on my end and get back with you. Thanks so much for your willingness to help!

  • Caleb Kingston Level 1 Level 1 (130 points)

    Okay so I went on youtube to find out how SUMIF works http://www.youtube.com/watch?v=V1acCft4fzY and then I was able to duplicate what you show here, Jerrold. Thanks for everything!


    Now, I'd still like a table that had a table like this split by month to see which months we have the least occupancy rate (if that makes sense)


    Thanks again for everything!

  • Jerrold Green1 Level 7 Level 7 (29,925 points)



    In my summary table, the expression for Days Occupied is:


    =SUMIF(Accepted Bookings :: C, A, Accepted Bookings :: G)


    The expression for Rate of Occ is:




    I'd like to help you with the breakdown by month, but it's not an easy putt. There was an almost identical question here in the past 12-18 months - you can probably find the discussion if you have some luck searching. I recall working on the solution and finding it quite complex. I don't think it's worth the effort in your case considering how sparse the data is, but that's your decision. You will be using SUMIFS (note the extra "S") to further constrain the summations to a particular month. But, the main issue is the problamatic reservation that spans the end of month; difficult, as you have speculated.


    Good luck,



  • Barry Level 7 Level 7 (29,210 points)

    Hi Caleb,


    If I'm reading your table correctly, the unit numbers are i column C, In date in column E, Out date in column F and duration of stay in column G.


    For stays that do not cross from one month to another, a simple SUMIFS statement woud work to calculate the number of occupied days in a given month. As you've found, theough, the stays that begn in one month and end in another are a bit trickier.


    One way to resolve the issue is to use an auxiliary table which calculates the occuped days (for all units) using a separate column for each month, then a summary table to sum the monthly data for each unit.

    Here's an example, using random data over a two and a half month span.

    Screen Shot 2013-05-07 at 6.49.55 PM.png

    Note that the dates in the header row of Aux and Summary are actual Date and Time Values set to the first of each month, and formatted to show only the month and year. These are used in the calculations.

    The only formula in Bookings is =F-E in each cell of column G (Stay).


    Aux has two formulas:


    A2, and filled down: =Bookings :: C2

    This simpy copies the values from column C of Bookings to column A of Aux.


    B2, and filled down and right:

    =IF(AND(Bookings :: $E2>=B$1,Bookings :: $F2<EOMONTH(B$1,0)+1),Bookings :: $F2-Bookings :: $E2,IF(AND(Bookings :: $E2<B$1,Bookings :: $F2>B$1),Bookings :: $F2-B$1,IF(AND(Bookings :: $E2<=EOMONTH(B$1,0),Bookings :: $F2>EOMONTH(B$1,0)),EOMONTH(B$1,0)+1-Bookings :: $E2,"no")))


    Same formula, separated into sections:


    IF(AND(Bookings :: $E2>=B$1,Bookings :: $F2<EOMONTH(B$1,0)+1),Bookings :: $F2-Bookings :: $E2,


    IF checks if the IN date AND the OUT Date are both in the month for this column. If so, the calculation is done and the formula is done; if not, control is passed to the next if:


    IF(AND(Bookings :: $E2<B$1,Bookings :: $F2>B$1),Bookings :: $F2-B$1,


    IF checks it the IN date is before the month for this column AND the OUT date is after the beginning of this month. If so,th calculaton is done and the formula is done; if not, contorl is passed to the thrid IF:


    IF(AND(Bookings :: $E2<=EOMONTH(B$1,0),Bookings :: $F2>EOMONTH(B$1,0)),EOMONTH(B$1,0)+1-Bookings :: $E2,


    IF checks if the IN Date is before the end of the month for this coumn AND the OUT date is after the end of the month. If so, the calculation is done; if not, the text blow is returned to the cell.




    This text was chosen to give me a visual indication of the result. In practice, I would replace it with the empty string ( "" ).


    Summary has a single formula:


    B2, and filled down and right:

    =IFERROR(DUR2DAYS(SUMIF(Aux :: $A,$A2,Aux :: B))/DAY(EOMONTH(B$1,0)),"")


    SUMIF sums the duatons of Stay for each unit in a specific month for each column.


    DUR2DAYS converts the duration to a number of days.


    DAY(EOMONTH(B$1,0) returns the number of days in the month whose date is in row 1.


    The first is divided by the second, and the result is formatted as percentage, with 0 decimal places.


    IFERROR traps the error that occurs when the source column on Aux contain only text values, and replaces the error message with the empty string.




  • Caleb Kingston Level 1 Level 1 (130 points)

    B2, and filled down and right:

    =IF(AND(Bookings :: $E2>=B$1,Bookings :: $F2<EOMONTH(B$1,0)+1),Bookings :: $F2-Bookings :: $E2,IF(AND(Bookings :: $E2<B$1,Bookings :: $F2>B$1),Bookings :: $F2-B$1,IF(AND(Bookings :: $E2<=EOMONTH(B$1,0),Bookings :: $F2>EOMONTH(B$1,0)),EOMONTH(B$1,0)+1-Bookings :: $E2,"no")))

    Hey Barry, thanks a ton for your help. I can tell it took a great amount of time in explaining this and it's all starting to make a lot of sense so thanks!


    I am wondering, however, what you mean by B$1 (I marked in bold on the quote). In the bookings table, B1 is blank. Sorry if this is a dumb question but I think if I can have this explained, I can finish wrapping my head around how to do this.


    Thanks again!


  • Barry Level 7 Level 7 (29,210 points)

    Hi Caleb,


    Cell references without a table name attached refer to cells on the same table as the formula. B1, on the Summary table, contains the Date and Time value January 1, 2013 00:00:00, formatted to show only the (short form) month and year values, joined by a dash.


    The $ operator holds that reference to row 1 as the formula is filled down the column. The lack of the $ operator before the B permits that column reference to change as the formula is filled to the right.





    PS: If you haven't yet done so, i'd strongly recommend downloding and reading at least the first four chapters of the Numbers '09 User Guide to learn some of the basics of working with Numbers. You'll find a link to the download in the Help menu in Numbers '09.


    Also available in the same place is the iWork Formulas and Functions User Guide, in which you'll find descriptions and usage examples for all the functions available in Numbers.



  • Caleb Kingston Level 1 Level 1 (130 points)

    Thanks Barry, I am going to do that tonight. I just received your email while googling where I can learn more about functions so my prayers have been answered. Thanks for your patience and help in the matter

  • Caleb Kingston Level 1 Level 1 (130 points)

    After reading through the users guide and Formulas and Functions users guide, as you suggested, I have found there is still much to learn. lol BUT I have gotten your formula to work and do exactly what I want so thank you very much! It's always a pleasure to be on the receiving end of support in these discussion forums


    Thanks, Barry!