Working out capacity between two dates?

**kennelman** · 02-08-2012, 07:14 AM

Hi, I know access would be a better option but i need to try and do this in excel. I'm trying to create a very basic booking sheet which will work out capacity for my kennels on each night. My columns will be Surname, Pets Name, Arrival Date and Departure Date. On another sheet I will have all my columns dated 1st, 2nd 3rd etc to create the days in a month.
Is there anyway excel will calculate nightly capacity from the two dates on each line i.e. Arrival and departure date and enter it into my calendar?
My knowledge of excel is fairly basic so apologies if I've asked any daft questions!

Many thanks

**kennelman** · 02-08-2012, 05:26 PM

Can anybody help?????

**todmac** · 02-08-2012, 08:01 PM

Can you put up an example of your worksheet and i can better help you....
Should not be difficult, but easier to give you an explantion if i can see better what you are talking about.

**kennelman** · 02-08-2012, 09:04 PM

My columns would be:

Column A Column B Column C Column D Column E
Surname Pet Arrival Date Departure Date Number of nights

If I then had a sheet with columns 1 - 30/31 for the dates of the month could excel calculate the capacity on each night using the data in columns C & D above?

Sorry i can't put a worksheet up for you but hope this will do.
Thanks for looking at this for me.

**DBY** · 02-09-2012, 10:57 AM

Hi
Do you have Excel 2010? If so you can use countifs to count the number of bookings on or between 2 dates and put them into a table as you describe with days across columns and months in rows.

**kennelman** · 02-09-2012, 12:44 PM

So if i have two dates i.e. 02/03/12 and 05/02/12 how would i use the countifs formula to tell me the capacity on the 1st, 2nd 3rd etc?

I can get it to count 02/03/12 and 05/03/12 but not the dates in between?

**DBY** · 02-09-2012, 12:56 PM

Hello
I've put together an example file for you to look at. See if this is what you're looking for. You'll get some idea from the formulas.

**kennelman** · 02-09-2012, 01:24 PM

OMG Thank you so much DBY, I've been struggling with this for weeks. I really can't thank you enough. Looking at that formula I don't think I would of ever made it! I'm going to see if I can add nightly cost's to it and then produce a turnover report like the calander.