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

Converting imported data from decimal years to years, day-of-year, and dates, need better method.

Converting imported data from decimal years to years, day-of-year, and dates, need better method.

Converting imported data from decimal years to years, day-of-year, and dates, need better method.

(OP)
I have many years (1979 to present) of ice cap area and temperature data available from a couple of different government web sites.

Their "date" field is four-decimal place with the year in the first 4 characters, and the day-of-year as the decimal: 1979. 8904, 2015.1425, 2014.9727, 2015.0027, etc.

The decimal date is the "A" column.

Right now, to create a useable day-of-year for row 456 for example, my "B" column is =ROUND(((A456-2014)*365+1),1)

and my "F" column is
=DATE(2014,1,B456)


This "sort of" works, but as each year changes, the formula must be editted. I don't think this method is the most efficient either. I use the "round" function because otherwise I get duplicate days.

Is there a simpler, less kluged formula that also works for the leap years in 1992, 1988, 1996, 2004, 2008, 2012?

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

see if the attached works.
Column B -- fraction of the year
Column C -- Jan 1/1 of the year
Column D -- Jan 1/1 of the next year
Column E -- builds the Excel datecode from the above

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

btw, what I did assumes that the original decimal year's fraction was explicitly calculated based on the actual number of days in that year, so 1980.8904
would not be 11/21/80 11:54 PM, since the date code would need to be 1980.890699441, not 1980.8904

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

(OP)
This is, for example, the raw data for the "cross-over" from 2011 (2011 Dec 31) to (2012 Jan 06), so each year this particular lab is using .0000 for Jan 01.

2011.9945 0.3402750 5.4454126 5.1051378
2011.9973 0.3926353 5.3707852 4.9781499
2012.0000 0.4330167 5.2749600 4.8419433
2012.0027 0.5474204 5.2029924 4.6555719
2012.0055 0.5565740 5.1040874 4.5475135
2012.0082 0.5970423 5.0354552 4.4384131
2012.0110 0.6305095 4.9548101 4.3243008
2012.0137 0.6226311 4.8471675 4.2245364

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

(OP)

Quote (IRStuff)

btw, what I did assumes that the original decimal year's fraction was explicitly calculated based on the actual number of days in that year, so 1980.8904
would not be 11/21/80 11:54 PM,

That appears correct. Their data is issued on a daily basis, and the issued "year.decimal" field is truncated at 4 decimal places.

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

Did you try the worksheet?

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

(OP)
Working through it, I'll need to come back to it later tonight.

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

If the year date number is always set to the start of the day there will be some rounding issues, which can be avoided by changing the last column in IRstuff's spreadsheet to:
=ROUND((D1-C1)*B1,0)+C1
which should return 00:00 on the correct day for any day and year.

Note that if the year number includes the time of day, any number set in the afternoon would be rounded up to the next day.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

The start of day is always an integer, there is no rounding for day numbers; that's guaranteed by the format, since the format is ddddd.fffff, where ddddd is the integer number of days since Jan 1, 1900, and fffff is the fraction of each day. Columns C and D are guaranteed by the concatenation to produce integer values.

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

The problem is that the day number will sometimes evaluate to just under the value for the intended day, so will be treated as the previous day. The round function will round it up to the correct day.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

If you say so, but the constructs in columns C and D are specifically written to result in ddddd.0, and 1/1/YYYY is, by definition, in Excel to be an integer value.

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

Quote:

If you say so, but the constructs in columns C and D are specifically written to result in ddddd.0, and 1/1/YYYY is, by definition, in Excel to be an integer value.

Columns C & D are integers, but you multiply by a decimal approximation to n/365 or n/366, so half the time this will return a value slightly under n, which will be truncated to n-1.

If you try it on the data supplied you will find it repeats some days and then skips a day.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

Sorry, I misunderstood you; that wasn't a problem with Excel, per se, but also with the data's insufficient precision to get closer to the start of day.

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

Quote:

Sorry, I misunderstood you; that wasn't a problem with Excel, per se, but also with the data's insufficient precision to get closer to the start of day.

It's not an Excel problem, any system using floating point numbers with a finite number of decimal places will have the same problem if the input decimal is intended to represent the exact start of the day.

The Round function fixes the problem on the supplied data. An alternative that would work both if the decimal was set to the time it was actually entered or if set to the exact start of day would be to use Round with a higher level of precision (say 5 places), or just add 0.00001 to all the numbers.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

(OP)
Using that method, will monitor for the duplicate days for the ROUND function.

Many thanks to all.

RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.

It could have been done quite easily, if that was their intent. They could have simply coded it as YYYY.DDD, where DDD ranges from 001 to 366. They pretty much had all the information to get the fraction of the year calculation, so this coding would have been perfectly precise. However, I suspect that they left it that way so that they could increase or decrease the number of readings per year, which would have required floating point, but at a higher precision.

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

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