I have an excel file (10) with a column of date in format yyyy-mm-dd hh: mm: ss. I want to convert to the standard date/time format - that is mm-dd-yy hh: mm: ss.

Posted: 19 Feb 2012

Tags: I have an excel file 10 with a column of date in format yyyymmdd hh mm ss I want to convert to the standard datetime format that is mmddyy hh mm ss have excel file with column date format yyyymmdd want convert the standard datetime that mmddyy

Source: www.belltechsystems.com

Question:

Downloads have the system date is 2012-mm-dd hh: mm: ss General format. I need to convert to the format of date normal mm-dd-yy hh: mm: ss.

Thank you

Answer:

Select your cells and run this small macro:

Sub ReFormat()
Dim r As Range, v As String
For each r in selection
v = r.Text
year = Left (v, 4)
MN = Mid (v, 6, 2)
dy = Mid (v, 9, 2)
HR = Mid (v, 12, 2)
Mint = Mid (v, 15, 2)
SC = Right (v, 2)
r.NumberFormat = "mm-dd-yy hh: mm: ss"
r.value = DateSerial (year, mn, dy) + TimeSerial (RH, mint, sc)
Next
End Sub

Macros are very easy to install and use:

1 ALT - F11 brings the VBE window
2 ALT - I
ALT - M opens a new module
3 paste these things in and close the VBE window

If you save the workbook, the macro will be saved with it.

To delete the macro:

1. open the VBE window as above
2 erase the code
3 Close the VBE window

To use the Excel macro:

1 ALT - F8
2. Select the macro
3 Key RUN

For more information about macros in General, see:

http://www.MVPs.org/dmcritchie/Excel/getstarted.htm

• Pages:
• 1

0 Comments

Add Your Comment !
Name
Your Comment
Security Code Enter the same letters as you see in image
	Please Wait...