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?
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.
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.
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.
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.
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.
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.
RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.
=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.
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.
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.
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.
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.
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.
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.
Many thanks to all.
RE: Converting imported data from decimal years to years, day-of-year, and dates, need better method.
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