I have a series of dates that I need to convert. What is the best formula or way to convert the following. I have included some examples below.
The 110 at the beginning represents 2010 and the 111 at the beginning represents 2011.
110356
110351
110364
111003
Also would the above actually be considered Julian dates? I have seen them with 2 digits and 4 digits representing the year but not normally 3.
Last edited by dotsofcolor; 02-05-2012 at 11:02 PM.
What dates do those strings convert to?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
The strings should convert to the following I believe if I am converting them correctly.
110356 12/22/2010
110351 12/17/2010
110364 12/30/2010
111003 1/3/2011
I have this formula to work with which seems to be working but I can't help wondering if this is the best way to work with these types of Julian Dates especially when there are as many as I have to work with, and it's a lot.
Any suggestions on another method to work with this?=DATE(YEAR("01/01/"&TEXT(1900+INT(E22/1000),0)),MONTH("01/01/"&TEXT(1900+INT(E22/1000),0)),DAY("01/01/"&TEXT(1900+INT(E22/1000),0)))+MOD(E22,1000)-1
You'd need to specify the format, not anyone else here. If the last 3 digits are Julian dates, then it appears the next 2 digits before that would be the year within the specified century, and I'd guess the 1 meant 2000 thru 2099, so 0 would mean 1900 thru 1999, so presumably 2-digit years would be between 1900 and 1999 since the leading 0 for 099002 for 2 Jan 1999 may have been dropped. Further, I'd guess 4-digit years are as-is, so 2011364 would be 30 Dec 2011.Originally Posted by dotsofcolor
If so, try
=DATE(IF(LEN(A1)<7,1899,0)+INT(A1/1000),12,31)+MOD(A1,1000)
This one "seems" to work:
=DATE(MID(A1,2,2)+2000,1,0)+RIGHT(A1,3)
---
Ben Van Johnson
Please check my attached file for solution. You need formula==DATE(20&MID(D2,2,2),1+IFERROR(MATCH(VALUE(RIGHT(D2,3)),D5:D16,1),0),VALUE(RIGHT(D2,3))-IFERROR(VLOOKUP(MATCH(VALUE(RIGHT(D2,3)),D5:D16,1),B5:D16,3,FALSE),0))
You may also interest in:
Extract report from a date range in Ms Excel
Combine duplicate Data in Ms Excel
Last edited by holin_168; 02-03-2012 at 11:31 PM.
It should unless there were any dates in the 1900s. Then it may fail.
I tried them all and they worked great.All of them helped me with the direction I needed to go and I learned a few things along the way. Always great help in this forum and it's really appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks