Date Format

[NotGood@All]

I have inherited a workbook that has some columns where the person entered
the date as 5/21, 6/10…. All the dates are 2007. Is there a way for me to
add “2007†to each of the rows so the date appears as "5/21/2007"

 

[Bernard Liengme]

Let us first determine if the entries are text or formatted dates.
If the fist date is in A1 what does the formula =ISNUMERIC(A1) return?
If TRUE then all you need do is reformat the cells

 

[David Biddulph]

If your data are text strings,
=DATE(2007,LEFT(A1,FIND("/",A1)-1),RIGHT(A1,LEN(A1)-FIND("/",A1))) and
format appropriately,
or =A1&"/2007" if you just want the result as a text string.

 

[Gord Dibben]

I'll bet Bernard meant ISNUMBER


Gord

 

[Bernard Liengme]

Yeep! and every time he type ISNUMERIC (which seems better name to him) he
gets a #NAME? error and he mutters to himself profound philosophical words.
Happy New Year, Gord

 

[Gord Dibben]

Been working with VBA where ISNUMERIC is valid.

Happy New Year to you also Bernard.

Getting better every day as the snow leaves the ground.


Gord

 

[NotGood@All]

Thank you all, I did use "isnumber". Some of the fields (45000) are date,
some text, and some general. So my question now is can something be written
to first; format each cell to a date format, second, keep the first 5
characters, then add "/2007"

 

[David Biddulph]

If you want to turn a date into text in a specific date format, use the TEXT
function.
If you want the first 5 characters, use the LEFT function.
If you want to concatenate an extra string, use the CONCATENATE function, or
the & operator as shown below.

If you want to do things the other way round you can use the DAY and MONTH
functions to extract those parts, and use
DATE(2007,your_month,your_day) to get a real date in 2007.

If you don't know how any of those functions work, they are all standard
Excel functions and shown in Excel help.