If you put the year in cell A1 then use
and format as a number
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.
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.
You can then copy down the formula in column C for all applicable cells for your year data in column B.Code:=mod(B8,4)
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)