text to date

[johngio]

HI all,

I have some values such as:

Wed-11-Oct 06:29
Thu-12-Oct 11:37

These are manually entered as text. I would like to have excel recognise them as dates and times separately, as I have a column with dates such as 11/10/2006 (and I want to look for this date to see if they are the same). However I am having trouble converting this text into a date format (I have managed to create a separate column with the time component).

Any ideas?

Thanks

John

 

[johngio]

I can create a date by using the following code:
(in cell AB10)
=MID(AB14,5,2)&MID(AE$11,1,1)&MID(AB14,8,3)&MID(AE$11,1,1)&2006

This will give me: 11-Oct-2006

However, when I try DATEVALUE("AB10") I get a #VALUE! error. Must be one of those formatting things in Excel.

Any tips?

Thanks

 

[Joe4]

You are on the right track, but you need to convert "Oct" to the numeric equivalent of 10 to get DateValue to work.

 

[johngio]

Hmmm,

Even If I change the formula to :

=MID(AB14,5,2)&MID(AE$11,1,1)&10MID(AE$11,1,1)&2006

and try DATEVALUE("AB10") I get the #VALUE! error . . .

Are there any easy ways to convert Jan/Feb/Mar (as text) to 1,2,3, etc?

Cheers

 

[xapie128]

However, when I try DATEVALUE("AB10") I get a #VALUE! error. Must be one of those formatting things in Excel.

You are on the exact track, just drop the " " quote marks...

DATEVALUE(AB10) should work fine (then format the cell with a date format).

.

 

[johngio]

Got it now :P

Cheers all!

 

[Joe4]

Interesting, it works for:
11-Oct-2006
but not for
Oct-11-2006

I thought it might have to be reversed to work here in the US, but apparently not...