Archive of Mr Excel Message Board

Back to Data in Excel archive index
Back to archive home

Converting Cell Data (QuiOui)

Posted by DSM on January 30, 2002 8:37 AM

Ok, again... thanks for the help on my previous issue. I have another questions! In this instance, I have dates that are in the form of yyyymmdd. I used the previous tip to convert my hhmmss to hh:mm:ss and placed that data in a seperate column. First, how can I convert the yyyymmdd into an easily recognized form (maybe mm/dd/yy or something) and then calculate working days from the date and time. Do I, and how would I, figure that from the two seperate columns (date col. and time col.)?

Re: Converting Cell Data (QuiOui)

Posted by Mark W. on January 30, 2002 8:47 AM

Your dates can be converted in place using the
Text to Columns wizard...

1. After selecting the cell range containing
the your dates, choose the Data | Text to Columns...
menu command
2. At Step 1 of 3 choose "Fixed width" and press
[ Next > ]
3. At Step 3 of 3 choose the YMD date format
and press [ Finish ].

You can combine your time and date into a single
cell by adding them together (e.g., =A1+B1).
This combined value can be used in subsequent
calculations.

Re: Converting Cell Data (QuiOui)

Posted by DK on January 30, 2002 8:48 AM

Well, I'm sure there are plenty of other ways of doing this but here's one method.

Assuming your 'date' is in cell A1 try this formula to convert it to a date:-

=DATEVALUE(MID(A1,5,2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,4))

HTH,
D

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.