INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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)

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?

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

Quote:

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/

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

(OP)
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

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

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: 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)

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.

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

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.

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

@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

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

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:

Quote (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/

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

Thank IDS,

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

(OP)
IDS.

Marked, I will try that.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close