Calculating number of rotations per month

G

Guest

Guest
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
Marcus
I have e-mailed you a spreadsheet which I think solves your problem
regards
Derek
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top