Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
(OP)
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?
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?





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

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
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/
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
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
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
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.
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
Bet regards, Morten
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
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/
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
RE: Minor Excel Dilema: Why does Excel 2007 think 2014 has 366 days? ( Day => Date default function)
Marked, I will try that.