VB script to format date

hinkwale

New Member
Joined
May 28, 2012
Messages
45
I have a table in access 2007

sample table

Column A Column B Column C
Servername</SPAN>Backup Date</SPAN>Expiration</SPAN>
cmc2asff1-netbackup</SPAN>20030927205029000</SPAN>20110501030000000</SPAN>
cmc2asff1-netbackup</SPAN>20031104121955000</SPAN>20110501030000000</SPAN>
cmc2asff1-netbackup</SPAN>20040328092657000</SPAN>20110501030000000</SPAN>
cmc2asff1-netbackup</SPAN>20060702042604000</SPAN>20090701082604000</SPAN>
cmc2asff1-netbackup</SPAN>20060728230901000</SPAN>20090728030901000</SPAN>
cmc2asff1-netbackup</SPAN>20060825231204000</SPAN>20090825031204000</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Iwould like to have a macro that formats Columns B & C to for example
09/27/2003 20:50:29

Thanking you in anticpation for your help

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You didn't mention the data type currently on the field (Text or Numeric?).
And you didn't mention if you need it to be returned in a Date format of Text format (if you are doing any sort of calculations on the field, you may need it to be Date/Time).

Here is a calculated field you can use in a query that will return it as Text exactly like you want it to look:
Calc_Date: Mid([BackupDate],5,2) & "/" & Mid([BackupDate],7,2) & "/" & Left([BackupDate],4) & " " & Mid([BackupDate],9,2) & ":" & Mid([BackupDate],11,2) & ":" & Mid([BackupDate],13,2)
 
Upvote 0

Forum statistics

Threads
1,212,045
Messages
6,105,579
Members
447,972
Latest member
carrieann

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
Back
Top