Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)

[racookpe1978]

On a scale of 1.0 to important, this probably doesn't rank very high, but i'm puzzled.

Excel 2007, Windows computer. One of my input variables is "day-of-year" as a number from 1 to 365, 366 in a leap year. One output is the (Date) function in the next cellover, just so I can "see" both formats.

But, 58, 59, 60, 61, 62 are giving me Feb 29 - apparently for this year (20140 since I'm not assigning any other year value anywhere. Wouldn't the expected result be 1-366 for 2012 or 2016 when Feb 29 exists, and 1-365 for the rest of the years?

 

[IRstuff]

A single value of day of the year will generally get converted to the day of the year 1900, since that's the way Excel's datacodes are formatted. 1900 was a leap year

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[IRstuff]

btw, 1/1/2014's datecode is 41640


TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[IDS]

1900 was a leap year

Actually it wasn't, but Excel (by default) pretends it was, because that was what Lotus 123 did.

But yes, if you format the number 60 to display as a full date it will show 29 Feb 1900 (which didn't exist).

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[racookpe1978]

Well, since this is not going to be a commercial product, ans since that means I only have to document it for one user (or only a very few others!) looking at heat losses in the arctic and antarctic, I think I will do the following. Critique it thoroughly though ( since I respect the value of your independent opinion of my own somewhat pre-judged ideas)...

The DOY (day-of-year) goes in cell H1 from the user, expecting 1-365, or 1-366.

Leap_Year is defined in cell H3 as LY by the user, depending on what year is being checked.
Binary? 1 (yes) or 0 (no)

I want to display the calendar date in cell I1, next to the DOY in H1.

Formula in I1 then
=IF(DOY<=59,DOY,(DOY-(LY-1)))

So, if DOY <= 59, 'date' displays date. Always 58 => 27 Feb; 59 => 28 Feb.
If LY = 0, not a leap year, so "date" displays DOY-(0-1) = (DOY+1) and 60 => 1 March
If Ly = 1, is a leap year, so "date" displays DOY -(1-1) = (DOY+0) and 60 => 29 Feb; 61 = 1 March

 

[IRstuff]

Alternately, you could just add in the correct number of days since 1/1/1900, based on the actual year. Seems to me that having the user just input the year they're interested in would be easier than having them figure whether it was a leap year or not.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[jghrist]

If you are going to have the user input whether or not it is a leap year, why not have him input the year instead? Then you can just add DOY-1 to DATE(YEAR,1,1) and display with whatever date format you want.

 

[MintJulep]

How to get day of year: Look at a calendar and count.

How to get "Was it a leap year"?: Google search, look at a calendar for that year and see if there is Feb 29.

Just allow the user to enter complete dates as user input and don't make the user go someplace else to determine what the correct input should be.

 

[MortenA]

@mintjulep - that calenday may have "forgotten" the 50 year rule: if the year is dividable (dont know if this is the english term) by 50 -its NOT a leap year (and then theres the 200 year rule: If it is diviable by 200 then it is anyway.

Bet regards, Morten

 

[IDS]

There is no 50 year rule or 200 year rule (not in the calendar most of us use anyway. There is a 100 year rule and a 400 year rule though, From Wikipedia:

if year is divisible by 400 then leap year is true
else if year is divisible by 100 then leap year is false
else if year is divisible by 4 then leap year is true
else leap year is false

Excel knows this, except that (as I mentioned before), it ignored the 100 year rule in 1900 to maintain compatibility with Lotus 123.

What's wrong with =Date(year,1,1)+DOY-1 (formatted as a date), as has been suggested? This will work for any year after 1900.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[MortenA]

Thank IDS,

 

[racookpe1978]

IDS.

Marked, I will try that.