Anyone have a a single Excel formula to convert the typical array data: "Year, Day of Year, Hour/min" to an MS Excel date?
The one we have doesn't really use the year and messes up on leap years etc.
we use the following formula in Excel:
=DATE(B3874,1,C3874)+INT(D3874/100)/24+MOD(D3874,100)/1440
where
Column B is Year
Column C is Julian Day
Column D is Time
Hope this helps.
Yes, it helps thank you.
I copied this from a source I don't remember (may have benn CS website) and it has seemed to work well.
=(B3-1900)*365+1+INT((B3-1901)/4)+C3+INT(D3/100)/24+(D3/100-INT(D3/100))*100/60/24
Column B is Year
Column C is Julian Day
Column D is Time (HHMM)