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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

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
58,966
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
58,966
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...
 

Forum statistics

Threads
1,141,227
Messages
5,705,137
Members
421,380
Latest member
Nuwan Sanjeewa Aponso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top