Date Format
 

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"
--
NotGood@All

Date Format
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"NotGood@All" wrote in message
...
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"
--
NotGood@All

Date Format
 

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.
--
David Biddulph


"NotGood@All" wrote in message
...
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"
--
NotGood@All

Date Format
 

I'll bet Bernard meant ISNUMBER


Gord

On Tue, 6 Jan 2009 13:12:50 -0400, "Bernard Liengme"
wrote:

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

Date Format
 

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
--
Bernard
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I'll bet Bernard meant ISNUMBER


Gord

On Tue, 6 Jan 2009 13:12:50 -0400, "Bernard Liengme"
wrote:

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

Date Format
 

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

On Tue, 6 Jan 2009 17:00:51 -0400, "Bernard Liengme"
wrote:

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

Date Format
 

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"
--
NotGood@All


"David Biddulph" wrote:

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.
--
David Biddulph


"NotGood@All" wrote in message
...
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"
--
NotGood@All

Date Format
 

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.
--
David Biddulph

NotGood@All wrote:
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"

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.
--
David Biddulph


"NotGood@All" wrote in message
...
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"
--
NotGood@All