Date formating question

[Dee2005]

Hey guys, I am new to Excel. have date and time in same coloumn (Dec 20 2005 12:30PM). How do I convert it into 12/10/2005 12:30PM.
There is some times 1 sometimes 2 spaces between month and date and year. What formula should I use.
Thanks in advance
DEE2005

 

[Scott Huish]

Hi, welcome to the board!

Do you always want to subtract 10 days?

Is the day of the month always 2 digits or can it be 1?

 

[Dee2005]

So Sorry guys

Sorry guys. I do not want to substract 10 days.
Just the format change to 12/20/2005 hh:mmAM/PM
Thanks pepper

 

[kijek]

Hi Dee2005,

How are you getting your date information? Is it being downloaded? If so, from where?

 

[Yogi Anand]

Hi Dee2005:

Welcome to MrExcel Board!

I don't know in what way you are using it ... that should determine whether you want a formula based approach or a macro for this. Anyway, following is a formula based solution

y051221h1.xls
	A	B	C	D
1
2	Dec 20 2005 12:30PM	12/20/2005 12:30PM
3	Dec 02 2005 12:30PM	12/02/2020 12:30PM
4	Dec 21 2005 06:18AM	12/21/2005 06:18AM
5
Sheet2 (2)

Is this what you are looking for?

I have made no attempt to simplify the formula. Practically speaking, one or more helper columns will be useful in avoiding redundant/repetitive calculations.

 

[arunjhalani]

Or simply by =text(a1,"mm/dd/yyyy") where A1 is the Cell u referred


Regards

Arun Jhalani
SAP CO Consultant

 

[Scott Huish]

arunjhalani:

That won't work on a text string

 

[Scott Huish]

If you want it functional as a date, this UDF should do it, format the result cells as mm/dd/yyyy h:mm AM/PM

Public Function convDate(a)
Dim y As String
y = Application.Trim(a)
convDate = DateValue(y) + TimeValue(y)
End Function

Book8
	A	B	C	D
1	Dec 20 2005 5:30PM	12/20/2005 5:30 PM
2	Dec 20 2006 8:00 AM	12/20/2006 8:00 AM
Sheet1