Thread: Mixed types and date conversions, including time stamps

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

Your approach works fine for me.

HTML Code: 

40547.5375	40547.5375	04-Jan-11	=IF(TYPE(A12)=2,DATEVALUE(MID(B12,4,2) &"/"& LEFT(B12,2)&"/"&MID(B12,7,4)),B12)
03/31/2011 22:51	03/31/2011 22:51	31-Mar-11	
3/31/2011 22:33	03/31/2011 22:33	31-Mar-11	
31/03/2011	40633	31-Mar-11	

I suspect it relates to your Regional settings and that you're getting cells containing TEXT which look to be in an American style date format.

Works for me because I've got UK settings and the dates are converted to UK style ... at least that's what I think it is.

Regards

HTML Code: 

04/01/2011	40547		04/01/2011  Tuesday, 04 January, 2011
01/04/2011	40634		01/04/2011  Friday, 01 April, 2011

The number you have posted is the day number for 4 January 2011, as shown above.

The above representations are a) a typed in date, b) the day number for that date, and c) a formatted representation of the date: dd/mm/yyyy dddd, dd mmmm, yyyy

I think your headache will continue if you are trying to convert that number into 1 April ... or am I just beginning to see the significance of the question? ;-)

Regards

HTML Code: 

03/11/2011	40850.74722		03/11/2011  Thursday, 03 November, 2011

Regards

Where does your raw data originate from?

Is it a text file?

Can you upload a sample?

Hi Rebecca

See the attached workbook with your dates in them. I think you'll find that you do have November dates. Whether you should have or not is another matter.

I used:

=IF(TYPE(A2)=1,INT(A2),DATE(MID(A2,FIND("/",A2,FIND("/",A2)+1)+1,4),IF(FIND("/",A2)=3,LEFT(A2,2),LEFT(A2,1)),MID(A2,FIND("/",A2)+1,2)))

to check and convert the dates. See the attached workbook.


Regards

You're welcome. At least now you know where the problem lies.

It's interesting that the dates that are in September, October and November are the ones in numeric format and look as though they must have come out of another Excel workbook. You can almost excuse American style dates that need "manipulating".

All the best telling the business what they can do with their data ;-)

Regards