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

[racookpe1978]

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?

 

[IRstuff]

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

Need help writing a question or understanding a reply? forum1529


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

 

[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, since the date code would need to be 1980.890699441, not 1980.8904



TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


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

 

[racookpe1978]

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

 

[racookpe1978]

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.

 

[IRstuff]

Did you try the worksheet?

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


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

 

[racookpe1978]

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

 

[IDS]

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/

 

[IRstuff]

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

Need help writing a question or understanding a reply? forum1529


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

 

[IDS]

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/

 

[IRstuff]

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

Need help writing a question or understanding a reply? forum1529


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

 

[IDS]

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/

 

[IRstuff]

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

Need help writing a question or understanding a reply? forum1529


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

 

[IDS]

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/

 

[racookpe1978]

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

Many thanks to all.

 

[IRstuff]

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

Need help writing a question or understanding a reply? forum1529


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