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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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
65,484
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
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
65,484
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,175,929
Messages
5,900,361
Members
434,828
Latest member
Thungerford

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