# Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

1. ## Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

I've been reading around and there seem to be a lot of formulas to fix parts of my date problem but not sure how to combine them all to get a date that excel recognizes. Would like to remove the time, including the AM or PM and then convert the Month (Jan) into the date (1). Once that is out of there DATEVALUE should do the trick. Any ideas?

Thanks in advance for the help, this one has me stumped...

2. ## Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

=--SUBSTITUTE(SUBSTITUTE(A1, "/", " "), " ", ", ", 2) and format as you wish.

3. ## Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

B5=your cell range of date (Jan/07/2013 11:55:43 AM )
=SUBSTITUTE(MID(B5,1,FIND(" ",B5)-1),MID(MID(B5,1,FIND(" ",B5)-1),1,FIND("/",MID(B5,1,FIND(" ",B5)-1))-1),MONTH(1&MID(B5,1,FIND("/",B5)-1)))

I hope your problem is resolved

4. ## Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

Perfect, thank you my friend!

5. ## Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

Hi dynastarsic,

I'm sure SHG wont mind me making one change to his formula.

=--INT(SUBSTITUTE(SUBSTITUTE(A1, "/", " "), " ", ", ", 2))

Adding the INT() function gets rid of the time at the end which you said you didn't need.

Regards,

David

When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.

6. ## Re: Convert Jan/07/2013 11:55:43 AM to a recognizable DATE

Thank you, David. You can remove the --.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1