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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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,123,380
Messages
5,601,285
Members
414,440
Latest member
Kim0204

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