Date formating question

Dee2005

New Member
Joined
Dec 21, 2005
Messages
2
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 21, 2005
Messages
2
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

New Member
Joined
Mar 20, 2004
Messages
10
Hi Dee2005,

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

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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
ABCD
1
2Dec 20 2005 12:30PM12/20/2005 12:30PM
3Dec 02 2005 12:30PM12/02/2020 12:30PM
4Dec 21 2005 06:18AM12/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

Board Regular
Joined
Oct 8, 2002
Messages
121
Or simply by =text(a1,"mm/dd/yyyy") where A1 is the Cell u referred


Regards

Arun Jhalani
SAP CO Consultant
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
arunjhalani:

That won't work on a text string
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Code:
Public Function convDate(a)
Dim y As String
y = Application.Trim(a)
convDate = DateValue(y) + TimeValue(y)
End Function
Book8
ABCD
1Dec 20 2005 5:30PM12/20/2005 5:30 PM
2Dec 20 2006 8:00 AM12/20/2006 8:00 AM
Sheet1
 

Watch MrExcel Video

Forum statistics

Threads
1,118,736
Messages
5,573,926
Members
412,555
Latest member
mark84
Top