# Thread: Apportioning days by year (2003)

1. ## 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?

2. ## 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)

3. ## 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

4. ## 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
•