Transfer dates to "calendar"

Hello everybody.

I'm new to the forums and I appreciate any imput.

I work in a travel agency, the way we keep track of how many people come and go is with a spreadsheet in which we have the client's number, arrival date, departure date, name etc. We manage around 4,000 people each year. What I want to do is find a way to transfer the duration of their trips to a calendar. I'm attaching pictures of both our main lista and the calendar I was thinking about.

Is there a formula or macro that can take those date values and transfer them to the other spread sheet? Or is there any other way in which I can know at any given time, how many people we have in house?

Thanks

Tunk

Conditional Formatting. Pictures aren't much help with spreadsheet problems though

This file shows you that it's possible.

I left the "calendar" on the same sheet, just put it in columns AA:FB.
It could be easily moved to another sheet.

You would need to hide columns AA:AF. They are used to sort the guests by arrival date. If you could guarantee that the "In" column would always be sorted by "In" date then column AA could be deleted.

If you don't understand how to move the formulas to your workbook, let me know.

Notes:
If you try to use my workbook:
1) you would want to hide columns AA:AF
2) my workbook only has 3 conditional formats so I could only put 3 colors on the "calendar". If you have more then you can add them.
3) I did not try to figure out how to get "October 2011", "November 2011", etc to include the correct columns. I figure that you can do that manually each month. If you need it automated, I can probably do it.
4) Copy AA76:FB76 down as many rows as you want (all the way to the bottom if you want). It shouldn't matter if there is nothing in columns A:Z for any rows with the formulas in them.
5) It will automatically adjust the "calendar" as people depart.

I just reread your message, and notice that you wanted to count how many people are in house.
To do that I would insert a column (say column "Z") with the formula in Z2: "=And($B2<=Today(),$B3>=Today())" then in some cell somewhere "=Countif($Z:$Z,TRUE)".
If desired, with a little work, you could put the # of people in house for every column in the "calendar" and see how the # changes from day to day.

I assume that the 2nd formula is "=Countif($Z:$Z,True)". It will always be the same #.
It's the 1st formula that determines what day it's counting. The formula I gave you only counts for "Today()". You could replace the "Today()" with "$Z$1" and type a date into $Z$1 and it would tell you how many on that date.

What did you expect to happen. It looks right to me.

Don't forget to mark the thread "Solved".