Announcement

Collapse
No announcement yet.

Number Of Days Per Year

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

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

    Comment


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

      Code:
      =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

      Comment


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

        Comment


        • #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)

          Comment


          • #6
            Re: Number Of Days Per Year

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

            Simon

            Comment


            • #7
              Re: Number Of Days Per Year

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

              Comment


              • #8
                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.
                Entia non sunt multiplicanda sine necessitate.

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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)

                    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

                    Comment


                    • #11
                      Re: Number Of Days Per Year

                      [QUOTE=Biz]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

                      Comment


                      • #12
                        Re: Number Of Days Per Year

                        Originally posted by Biz
                        = DATE(YEAR(A1)+1,1,1) - DATE(YEAR(A1),1,1)+MOD(YEAR(A1),4)
                        If I may, Biz, that's wrong. Excel knows what years are leap years, and your formula gives 368 days for 2007. I believe the formula I gave is correct.
                        Entia non sunt multiplicanda sine necessitate.

                        Comment


                        • #13
                          Re: Number Of Days Per Year

                          Originally posted by shg
                          If I may, Biz, that's wrong. Excel knows what years are leap years, and your formula gives 368 days for 2007. I believe the formula I gave is correct.
                          My apologies I had A1 as general format so when I input 2007 it displays 365.

                          Fix my error
                          =DATE(YEAR(A1)+1,1,1)-DATE(YEAR(A1),1,1)+IF(MOD(YEAR(A1),4)>1,0,MOD(YEAR(A1),4))

                          Biz

                          Comment


                          • #14
                            Re: Number Of Days Per Year

                            =DATE(YEAR(A1)+1,1,1)-DATE(YEAR(A1),1,1)+IF(MOD(YEAR(A1),4)>1,0,MOD(YEAR(A1),4))
                            That returns 366 for 2009, 2013, 2017 ...
                            Entia non sunt multiplicanda sine necessitate.

                            Comment


                            • #15


                              Re: Number Of Days Per Year

                              Originally posted by shg
                              That returns 366 for 2009, 2013, 2017 ...
                              Ok an error in formula
                              Now it should work

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

                              Biz

                              Comment

                              Working...
                              X