Date Time Formatting

uberathlete

Board Regular
Joined
Jul 11, 2007
Messages
117
Hi everyone. I've got this sheet of data in this form:

Excel Workbook
ABCDE
1SymbolDateHourPriceVolume
2BAXF02000010515001894.7950
3BAXF02000011314094894.7820
4BAXF02000011314403094.7925
5BAXF02000011314405494.7925
6BAXF02000011410204894.7850
7BAXF02000011410440094.79150
8BAXF02000011411261294.78100
9BAXF0200001179493694.7825
Sheet1


I would basically like to format the Date and Hour columns. The Dates are of the form YYYYMMDD and the Hours is of the form HHMMSS . How would I format the cells into a regular format (ie. MM/DD/YYYY) and also the hour (ie. h:mm:ss)? Any suggestions would be much appreciated. Thanks!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
I have users (stupid as they may be) who enter the date differently i.e. 31/08/2007 or 08/31/2007 or 31082007 or 3182007 etc you get the point. Is it possible to make it so it formats the date into dd.mm.yyyy no matter how they enter it?

Any suggestions on work arounds?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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