Hi,
I have a string of time stamped dates. Some are stored as TYPE=1 and some as TYPE=2. I need to convert these to a format of 31-Mar-11. I have successfully done it with all of the TYPE 2 values, but for some reason the TYPE 1 values are reverting to a DDMMYY version and hence, whenever the day is below 12, Mar 12 2011 will revert to 3-Dec-11. It's driving me insane.
On top of it, all of the raw dates that come in are formatted as MMDDYY, but some (and this isn't consistent), are coming in as "03312011 <timestamp>" and some as "3312011 <timestamp>". I've figured out this part but thinking it might play into my next set of formulas to convert the various types. please help! Below is an example of the formulas I'm using:
Raw data eg:
40547.5375
03/31/2011 22:51
3/31/2011 22:33
then i manipulate it with this formula:
=IF(MID(A12,2,1)="/",CONCATENATE("0",A12),A12)
OUTPUT:
4-Jan-11
03/31/2011 22:51
03/31/2011 22:33
and then i maniupate that out with this formula:
=IF(TYPE(A12)=2,DATEVALUE(MID(B12,4,2) &"/"& LEFT(B12,2)&"/"&MID(B12,7,4)),B12)
OUTPUT:
4-Jan-11
31-Mar-11
31-Mar-11
** as you can see, the top date should be April 1, 2011, and not Jan 4, 2011.
This is driving me crazy and I'm sure there has to be a better way than separating out the time stamp with text to columns (manual), then (manual again) using text to columns to convert to MMDDYY.
Please help!
Sincerely,
frustrated in syntax
Bookmarks