Find & Replace

B

BCC

Thanks for your help in advance.

Have cells containing various dates, Ex. 070306. Data in these cells copied
from a WORD Doc.

Want dates display as follows: 07/03/2006.

Help, Find & replace does not allow me to change different dates.
 
R

Ragdyer

Can't tell from your example if you're using:
mmddyy
OR
ddmmyy

But you can change your data to XL recognizable dates by using TTC, Text T
Columns.

Select the column of dates, and from the Menu bar:
<Data> <Text To Column> <Next> <Next>
Click on "Date",
And *make sure* that the next box contains the format designation of the
dates in your column, as they *now exist*, not what you're looking to
convert them to.

Then click <Finish>

You now have "legal" XL dates.
You can now custom format the column to display your dates in any form that
you wish.
 
B

BCC

Works perfect...thank You

Same type of question but using time. Ex. 1250.

Need to use this format - 12:50 hrs

Thanks
 
R

Ragdyer

Assume all times are 4 digits, ... i.e. 0745

With data starting in A1,
try this in B1:

=TEXT(TIME(LEFT(A1,2),RIGHT(A1,2),0),"hh:mm")&" Hours"

And copy down as needed.
 
D

Dave Peterson

Another one:

=--TEXT(A1,"00\:00")
And give it a custom format of:
[hh]:mm "hrs"
 
R

Rick Rothstein \(MVP - VB\)

Same type of question but using time. Ex. 1250.
Need to use this format - 12:50 hrs
Click to expand...

Here is a solution without using the TEXT function...

=LEFT(A1,2)&":"&RIGHT(A1,2)&" Hours"

assuming, of course, your time is always a 4-digit number.

Rick
 
Top