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.)?

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.

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