Calculating number of rotations per month
Calculating number of rotations per month
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Calculating number of rotations per month

  1. #1
    Guest

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com