Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Number Of Days Per Year

1. Member
Join Date
10th May 2006
Location
London, UK
Posts
57

## Number Of Days Per Year

I'm guessing this is a pretty simple query, but I can't seem to work it out, nor find the answer here.

If I have a year in a cell, say 2006, and in another cell I need a formula to tell me how many days are in that year. Just to try and calculate deliveries of things far into the future, so need to account for leap years.

Many thanks,

Simon

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Number Of Days Per Year

If you put the year in cell A1 then use

=DATE(A1+1,1,1)-DATE(A1+1,1,1)

and format as a number

Excel Video Tutorials / Excel Dashboards Reports

3. Senior Member
Join Date
27th June 2004
Location
Atlanta, Georgia (USA)
Posts
164

## Re: Number Of Days Per Year

Simon,

Using this definition from Wikipedia for leap years:

The Gregorian calendar, the current standard calendar in most of the world, adds a 29th day to February in all years evenly divisible by four, except for centennial years (those ending in -00), which receive the extra day only if they are evenly divisible by 400. Thus 1600, 2000 and 2400 are leap years but 1700, 1800, 1900 and 2100 are not.

you could use the following:

Let's assume your year is in column B starting in cell B8.

Let's put your evaluator in cell C8.

VB:
```
=mod(B8,4)

```
You can then copy down the formula in column C for all applicable cells for your year data in column B.

If C8 = 0, you have a leap year of 366 days, otherwise you have a common year of 365 days.

If you happen to be doing historical work, you also need to account for centenial years that do not divide evenly by 400.

HTH,

Far Farley

Excel Video Tutorials / Excel Dashboards Reports

4. Member
Join Date
10th May 2006
Location
London, UK
Posts
57

## Re: Number Of Days Per Year

Sorry, how do I do it when I have a full date in A1, eg 22-Dec-2006?

I've tried the following but it doesn't work...

=DATE(YEAR(A1)+1,1,1)-DATE(YEAR(A1)+1,1,1)

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Number Of Days Per Year

You are adding 1 to both DATE formulas, try this:

=DATE(YEAR(A1)+1,1,1)-DATE(YEAR(A1),1,1)

Excel Video Tutorials / Excel Dashboards Reports

6. Member
Join Date
10th May 2006
Location
London, UK
Posts
57

## Re: Number Of Days Per Year

OK cool, =MOD(YEAR(B8),4) works nicely, cheers all!

Simon

Excel Video Tutorials / Excel Dashboards Reports

7. Member
Join Date
10th May 2006
Location
London, UK
Posts
57

## Re: Number Of Days Per Year

And thanks Brian, also works! Think I'll use that one!

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Number Of Days Per Year

You had it almost correct - use

= DATE(YEAR(A1)+1,1,1) - DATE(YEAR(A1),1,1)

= DATE(YEAR(A1)+1,1,1) - DATE(YEAR(A1)+1,1,1)

which always returns 0.

Excel Video Tutorials / Excel Dashboards Reports

9. Established Member
Join Date
19th March 2003
Location
Bethlehem, South Africa
Posts
339

## Re: Number Of Days Per Year

Hi!

Because Excel automatically knows which years are leap years (except for 1900 that it had as a leap year to account for a Lotus 1-2-3 "bug"), you can simply deduct one date from another and format the cell with the answer as general/number, e.g. 01/01/2008-01/01/2007 = 365 and 01/01/2009-01/01/2008=366.

h

Excel Video Tutorials / Excel Dashboards Reports

10. Established Member
Join Date
18th January 2006
Posts
524

## Re: Number Of Days Per Year

Originally Posted by shg
You had it almost correct - use

= DATE(YEAR(A1)+1,1,1) - DATE(YEAR(A1),1,1)

= DATE(YEAR(A1)+1,1,1) - DATE(YEAR(A1)+1,1,1)

which always returns 0.
I think the formula below is more accurate

= DATE(YEAR(A1)+1,1,1) - DATE(YEAR(A1),1,1)+MOD(YEAR(A1),4)

MOD(YEAR(A1),4) checks for Leap Year.

Biz

Excel Video Tutorials / Excel Dashboards Reports

There are currently 3 users browsing this thread. (0 members and 3 guests)

#### Posting Permissions

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