Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apportioning days by year (2003)

    On the attached spreadsheet, I have a start and end date for each project. Column C calculates the total duration of the project in days. I have been asked to calculate how many days fall within each time period for each project. I have tried to figure out how to do this but am completely stuck. Can anybody help me please?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Apportioning days by year (2003)

    See the attached version. I placed the first day of 2008, 2009 and 2010 in cells; these are used in the formulas (otherwise I'd have had to use DATE(2008, 1, 1) etc. over and over in the formulas)
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Apportioning days by year (2003)

    Another way,

    Based on Hans table and formulae

    The columns heading used Custom Format in stead of the helping columns

    Custom Format in cells :

    1] E1 enter : 1/01/2008 and custom format to --> "Before "yyy

    2] F1 enter : 1/01/2008 and custom format to --> yyy

    3] G1 enter : 1/01/2009 and custom format to --> yyy

    4] H1 enter : 1/01/2010 and custom format to --> "After 2009"

    Modified Hans' Formulae :

    1] =MAX(MIN(E$1,C2)-B2,0)

    2] =MAX(MIN(G$1,C2)-MAX(F$1,B2),0)

    3] =MAX(MIN(H$1,C2)-MAX(G$1,B2),0)

    4] =MAX(C2-MAX(H$1,B2),0)



    Regards
    Bosco
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Jan 2007
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apportioning days by year (2003)

    Absolutely fantastic! Thank you both SO MUCH.

Posting Permissions

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