Mixed types and date conversions, including time stamps

[rharro]

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

[TMS]

Your approach works fine for me.

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

[rharro]

Hi!
thanks for your feedback.

I see that it did work for you for the March dates, but were you able to convert the raw data containing the April 1, 2011 date? That's the one I'm struggling with.

It outputs as 4-Jan-2011 rather than 1-Apr-2011...

I just didn't see that in the example you depicted below.

Thanks for your comment, and I will look into the regional settings.

I appreciate your feedback!

[TMS]

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

[rharro]

Ok, now I'm getting even more confused,

let's just take this example...

40850.74722

when i simply format the cell to a date (US english) it outputs as:
3-Nov-2011

The forumla bar reads:
03/11/2011 5:56:00 PM

I know for a fact that this date cannot be coming out of our system as Nov 3, 3011 as this date hasn't occurred yet. I believe that the this (40850.74722) is storing this date (03/11/2011 5:56:00 PM), which is the correct mmddyyy format i want, but for some reason, it reverts to ddmmyyy when I attempt to format it.

Thanks again for your input!

[TMS]

Regards

[TMS]

Where does your raw data originate from?

Is it a text file?

Can you upload a sample?

[rharro]

Hey there!
I work for a big global business and they have developed this internally - hence the inconsistencies. Below is an example of the raw data that I have to deal with...

40667.68403
40667.56875
40667.5625
40667.55417
40667.43194
40637.55694
40637.50556
40637.45486
40547.73264
40547.70069
40547.5375
03/31/2011 22:51
3/31/2011 22:33
3/31/2011 22:22
3/31/2011 21:57
3/31/2011 21:04
3/31/2011 19:59
3/31/2011 19:46
3/31/2011 19:14
3/31/2011 17:23
3/31/2011 17:12
3/31/2011 17:06
3/31/2011 16:57
3/31/2011 10:50
3/31/2011 10:08
3/31/2011 10:00
3/31/2011 9:50
3/31/2011 9:41
3/30/2011 18:41
3/29/2011 15:33
3/29/2011 13:11
3/29/2011 12:56
3/29/2011 11:03
3/29/2011 10:45
3/28/2011 16:37
3/24/2011 16:13
3/23/2011 12:58
3/22/2011 17:10
3/22/2011 14:38
3/22/2011 14:30
3/22/2011 9:11
3/22/2011 9:04
3/22/2011 8:47
3/21/2011 16:52
3/21/2011 14:08
3/18/2011 13:40
3/16/2011 17:01
3/16/2011 16:53
3/15/2011 12:32
3/15/2011 11:59
3/15/2011 11:25
3/14/2011 15:07
3/14/2011 10:48
3/14/2011 10:17
40850.74722
40850.74444
40850.64097
40850.625
40850.60139
40850.60069
40819.69028
40789.69236
40789.63958
40789.61597
40789.59167
40789.42708
40789.42639
40727.49653
40636.76181
40636.53264
40605.61736
40605.41597
40577.85833
40577.65208
40577.64861
40577.64236
40577.58889
40577.58125
40577.54792
40577.40347
40546.59514
40546.47292
40546.43333
2/28/2011 15:34
2/28/2011 13:28
2/28/2011 13:06
2/25/2011 13:33
2/24/2011 19:00
2/24/2011 14:00
2/24/2011 13:57

Thanks!
Rebecca

[TMS]

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

[rharro]

Thanks so much for all of your help. You are right. The long and short of it is that the data is corrupt. I'm going to have to make another work around that won't be as automated, but will fill the gap.... bugger it, I might tell my business that it's not possible unless they fix the garbage data!

Thanks again,
rharro

[TMS]

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