Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: Number Of Days Per Year

  1. #1
    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. #2
    Join Date
    23rd December 2003
    Location
    St. Louis, MO
    Posts
    446

    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. #3
    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. #4
    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. #5
    Join Date
    23rd December 2003
    Location
    St. Louis, MO
    Posts
    446

    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. #6
    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. #7
    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. #8
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Number Of Days Per Year

    You had it almost correct - use

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

    instead of

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

    which always returns 0.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    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. #10
    Join Date
    18th January 2006
    Posts
    524

    Re: Number Of Days Per Year

    Quote Originally Posted by shg
    You had it almost correct - use

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

    instead of

    = 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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: February 24th, 2008, 23:31
  2. Count Days In Specific Year Between Two Dates
    By torbenbb in forum EXCEL HELP
    Replies: 4
    Last Post: January 23rd, 2008, 00:08
  3. Convert Number To Year
    By dmatthew in forum Excel and/or Access Help
    Replies: 1
    Last Post: December 13th, 2006, 00:21
  4. year,month,week,days calculation
    By rpdtrooper in forum EXCEL HELP
    Replies: 3
    Last Post: August 3rd, 2006, 16:44
  5. Replies: 3
    Last Post: December 24th, 2004, 10:33

Bookmarks

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