Calculating number of rotations per month

Hello all,
Sorry to bother you, but I have a question in Excel that seems too difficult for me.

I work for a smaller airline and we use Excel as a database of our flights. I have a sheet with a starting date, say in column B, and a ending date, say in column C. Let's assume that the flight operates every Monday, that means that the dates in both column B and C are Mondays.

I have created one column for each month in the year, let's say they are in column D:O with January in D, February in E and so on.

What I am looking for is a formula and a solution to calculate how many trips this aircraft makes in January, in February and so on based on the period typed in column B and C. For example: If I select the starting date to be 2002-04-01 (Monday) and the end date to be 2002-05-20 (Monday) the same row should say "5" under April and "3" under May for the number of Mondays in April and May respectively.

If you have ANY suggestions or help at all for me, I would be MOST grateful!

Thanks in advance,
Marcus
marcus.karlsson@apollo.se

[Derek]

Hi Marcus
This is a very tricky little problem.
I don't have a cute answer, but this might give you some ideas

In E1:P1 type the month numbers (ie E1 = 1, F1 = 2 etc)
B1= "start date" column title
D1= "end date" column title
B2 = 1/4/02 (sorry I am only used to Aussie date format d/mm/yy)
D2= 20/5/02 (sorry I am only used to Aussie date format d/mm/yy)
B3 formula =IF(B2="","",IF($D$2>=B2+7,B2+7,""))
scroll formula down
C2 formula =IF(ISERROR(MONTH(B2)),"",MONTH(B2))
scroll formula down
E2 formula =COUNTIF($C$2:$C$25,E1)
scroll formula right

Note, all cells are formatted General, except columns B and D (use your own date format)

Maybe you can figure a way to use this by hiding the calculation columns/rows

Good Luck
regards
Derek

[Derek]

Marcus
I have e-mailed you a spreadsheet which I think solves your problem
regards
Derek