text to date

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
You are on the right track, but you need to convert "Oct" to the numeric equivalent of 10 to get DateValue to work.
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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

Board Regular
Joined
Aug 26, 2006
Messages
82

ADVERTISEMENT

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).

.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,593
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top