I have an excel spread sheet with multiple cells with different days & months
but all with 2007. I would like to change them all to 2008.

Just do a global replace on 2007 for 2008.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark" wrote in message
...
I have an excel spread sheet with multiple cells with different days &
months
but all with 2007. I would like to change them all to 2008.

Bob,

If I do a search and try to find 2007 it does not find anything. The format
for the cell is mm/dd/yy.

"Bob Phillips" wrote:

Just do a global replace on 2007 for 2008.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark" wrote in message
...
I have an excel spread sheet with multiple cells with different days &
months
but all with 2007. I would like to change them all to 2008.

Bob, won't that miss out 29th February?

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Bob Phillips" wrote in message
...
Just do a global replace on 2007 for 2008.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark" wrote in message
...
I have an excel spread sheet with multiple cells with different days &
months
but all with 2007. I would like to change them all to 2008.

There wasn't a February 29, 2007, so changing the 2007 to 2008 won't be a
problem.

2008 to 2009 may cause trouble next year at this time <bg.

Sandy Mann wrote:

Bob, won't that miss out 29th February?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Bob Phillips" wrote in message
...
Just do a global replace on 2007 for 2008.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark" wrote in message
...
I have an excel spread sheet with multiple cells with different days &
months
but all with 2007. I would like to change them all to 2008.




--

Dave Peterson

The format shouldn't matter.

When you show the Edit|Replace dialog, show the Options (if they're not visible)

Make sure you don't have "match entire cell contents" checked.

Mark wrote:

Bob,

If I do a search and try to find 2007 it does not find anything. The format
for the cell is mm/dd/yy.

"Bob Phillips" wrote:

Just do a global replace on 2007 for 2008.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mark" wrote in message
...
I have an excel spread sheet with multiple cells with different days &
months
but all with 2007. I would like to change them all to 2008.




--

Dave Peterson

I figured that only the existing dates need to be changed. Could be 3 dates or
365. Who knows if the OP needed an entry for 366 dates???

Sandy Mann wrote:

Dave Peterson" wrote in message
...
There wasn't a February 29, 2007

Yes I know but if you just replace 2007 with 2008 there isn't one in 2008
either.

I am assuming that by saying:

I have an excel spread sheet with multiple cells with different days &
months

Mark means ranges of dates, but perhaps he doesn't.
--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Dave Peterson" wrote in message
...
There wasn't a February 29, 2007, so changing the 2007 to 2008 won't be a
problem.

2008 to 2009 may cause trouble next year at this time <bg.

Sandy Mann wrote:

Bob, won't that miss out 29th February?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Bob Phillips" wrote in message
...
Just do a global replace on 2007 for 2008.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mark" wrote in message
...
I have an excel spread sheet with multiple cells with different days &
months
but all with 2007. I would like to change them all to 2008.




--

Dave Peterson


--

Dave Peterson

Fri, 4 Jan 2008 16:41:32 -0000 from Bob Phillips
:
Just do a global replace on 2007 for 2008.

Indeed, that works, but why? I thought all dates and times were
stored internally as real numbers.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Stan Brown" wrote in message
t...
Indeed, that works, but why? I thought all dates and times were
stored internally as real numbers.


I thought that as well. I have written many times in these NG's that dates
are just numbers formatted to look like numbers and so far no one has ever
corrected me but I am now having doubts if it is that simple.

I am sure that it was Dave Peterson who once said in answering one of my
posts *Dates are funny*

Certainly they seem to be treated differently by XL to all other entries.
If I enter 39452 in a cell and format it as Accounting I see £39,452.00 in
the cell but still simply 39452 in the formula bar. If I format the cell as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar. So I
am not simply seeing in the formula bar the number formatted to look like a
date by the formatting I applied or the number that I enter. It is almost
as if XL has changed the number into a string - well a funny sort of string
anyway - and applied the formatting to that. If this is what is really
being stored in the cell then it explains why the replacement works but XL
must then re-interpret the *string* back into a number whenever a formula
uses it.

As Dave said, "Dates are funny"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Stan Brown" wrote in message
t...
Fri, 4 Jan 2008 16:41:32 -0000 from Bob Phillips
:
Just do a global replace on 2007 for 2008.

Indeed, that works, but why? I thought all dates and times were
stored internally as real numbers.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/

Even VBA has a couple of different ways to get the value(?) of a date:

with activecell
.numberformat = "General" 'just not Text
.value = date
msgbox .value & vblf & .value2
end with




Sandy Mann wrote:

Stan Brown" wrote in message
t...
Indeed, that works, but why? I thought all dates and times were
stored internally as real numbers.


I thought that as well. I have written many times in these NG's that dates
are just numbers formatted to look like numbers and so far no one has ever
corrected me but I am now having doubts if it is that simple.

I am sure that it was Dave Peterson who once said in answering one of my
posts *Dates are funny*

Certainly they seem to be treated differently by XL to all other entries.
If I enter 39452 in a cell and format it as Accounting I see £39,452.00 in
the cell but still simply 39452 in the formula bar. If I format the cell as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar. So I
am not simply seeing in the formula bar the number formatted to look like a
date by the formatting I applied or the number that I enter. It is almost
as if XL has changed the number into a string - well a funny sort of string
anyway - and applied the formatting to that. If this is what is really
being stored in the cell then it explains why the replacement works but XL
must then re-interpret the *string* back into a number whenever a formula
uses it.

As Dave said, "Dates are funny"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Stan Brown" wrote in message
t...
Fri, 4 Jan 2008 16:41:32 -0000 from Bob Phillips
:
Just do a global replace on 2007 for 2008.

Indeed, that works, but why? I thought all dates and times were
stored internally as real numbers.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/


--

Dave Peterson

To add to this, if you run this macro on the cell with 39542 the value does not
change in the formula bar but will take the format in the cell.

Sub DateFormat()
Selection.NumberFormat = "dd-mmm-yyyy"
End Sub

Curious.


Gord

On Sat, 5 Jan 2008 16:26:44 -0000, "Sandy Mann"
wrote:

If I enter 39452 in a cell and format it as Accounting I see £39,452.00 in
the cell but still simply 39452 in the formula bar. If I format the cell as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar. So I
am not simply seeing in the formula bar the number formatted to look like a
date by the formatting I applied or the number that I enter

Gord,

It may be a version thing but in XL97 the Formula bar changes to 05/01/2008

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
To add to this, if you run this macro on the cell with 39542 the value
does not
change in the formula bar but will take the format in the cell.

Sub DateFormat()
Selection.NumberFormat = "dd-mmm-yyyy"
End Sub

Curious.


Gord

On Sat, 5 Jan 2008 16:26:44 -0000, "Sandy Mann"

wrote:

If I enter 39452 in a cell and format it as Accounting I see £39,452.00 in
the cell but still simply 39452 in the formula bar. If I format the cell
as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar. So
I
am not simply seeing in the formula bar the number formatted to look like
a
date by the formatting I applied or the number that I enter

As you said, dates are funny things

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
Even VBA has a couple of different ways to get the value(?) of a date:

with activecell
.numberformat = "General" 'just not Text
.value = date
msgbox .value & vblf & .value2
end with




Sandy Mann wrote:

Stan Brown" wrote in message
t...
Indeed, that works, but why? I thought all dates and times were
stored internally as real numbers.


I thought that as well. I have written many times in these NG's that
dates
are just numbers formatted to look like numbers and so far no one has
ever
corrected me but I am now having doubts if it is that simple.

I am sure that it was Dave Peterson who once said in answering one of my
posts *Dates are funny*

Certainly they seem to be treated differently by XL to all other entries.
If I enter 39452 in a cell and format it as Accounting I see £39,452.00
in
the cell but still simply 39452 in the formula bar. If I format the cell
as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar.
So I
am not simply seeing in the formula bar the number formatted to look like
a
date by the formatting I applied or the number that I enter. It is
almost
as if XL has changed the number into a string - well a funny sort of
string
anyway - and applied the formatting to that. If this is what is really
being stored in the cell then it explains why the replacement works but
XL
must then re-interpret the *string* back into a number whenever a formula
uses it.

As Dave said, "Dates are funny"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Stan Brown" wrote in message
t...
Fri, 4 Jan 2008 16:41:32 -0000 from Bob Phillips
:
Just do a global replace on 2007 for 2008.

Indeed, that works, but why? I thought all dates and times were
stored internally as real numbers.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work." -- Marie Dressler, in /Dinner at Eight/


--

Dave Peterson

It changed for me in my short tests.

I wonder what happens if you update the display (scroll up/down)?

Or even save, close and reopen the workbook?

Gord Dibben wrote:

To add to this, if you run this macro on the cell with 39542 the value does not
change in the formula bar but will take the format in the cell.

Sub DateFormat()
Selection.NumberFormat = "dd-mmm-yyyy"
End Sub

Curious.

Gord

On Sat, 5 Jan 2008 16:26:44 -0000, "Sandy Mann"
wrote:

If I enter 39452 in a cell and format it as Accounting I see £39,452.00 in
the cell but still simply 39452 in the formula bar. If I format the cell as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar. So I
am not simply seeing in the formula bar the number formatted to look like a
date by the formatting I applied or the number that I enter

--

Dave Peterson

Hi Dave,

No it remained the same.

Thinking that it may be because I had British Date format set I changed
Regional Option in the Regional and Language to English (United States), the
Short date format to mm/dd/yy and the Long date format to mm/dd/yyyy

Still the same.

Thinking that it may be because VBA is American date format eccentric I
changed Gord's code to:
Selection.NumberFormat = "mmm-dd-yyyy"

Still the same.

I closed and opened XL - still the same.

I closed & opened Windows - still the same.

Changed Gord's code back to what it was origially - still the same

The sun shine for other people.......... <g

Were you testing it in XL97?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Dave Peterson" wrote in message
...
It changed for me in my short tests.

I wonder what happens if you update the display (scroll up/down)?

Or even save, close and reopen the workbook?

Gord Dibben wrote:

To add to this, if you run this macro on the cell with 39542 the value
does not
change in the formula bar but will take the format in the cell.

Sub DateFormat()
Selection.NumberFormat = "dd-mmm-yyyy"
End Sub

Curious.

Gord

On Sat, 5 Jan 2008 16:26:44 -0000, "Sandy Mann"

wrote:

If I enter 39452 in a cell and format it as Accounting I see £39,452.00
in
the cell but still simply 39452 in the formula bar. If I format the
cell as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar.
So I
am not simply seeing in the formula bar the number formatted to look
like a
date by the formatting I applied or the number that I enter

--

Dave Peterson

Oh, oh.

I was replying to Gord's post. The formula bar changed for me, too.

(We're on the same side fighting with Gord <vvbg!)

And I used xl2003 when the formulabar changed to show the date.



Sandy Mann wrote:

Hi Dave,

No it remained the same.

Thinking that it may be because I had British Date format set I changed
Regional Option in the Regional and Language to English (United States), the
Short date format to mm/dd/yy and the Long date format to mm/dd/yyyy

Still the same.

Thinking that it may be because VBA is American date format eccentric I
changed Gord's code to:
Selection.NumberFormat = "mmm-dd-yyyy"

Still the same.

I closed and opened XL - still the same.

I closed & opened Windows - still the same.

Changed Gord's code back to what it was origially - still the same

The sun shine for other people.......... <g

Were you testing it in XL97?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"Dave Peterson" wrote in message
...
It changed for me in my short tests.

I wonder what happens if you update the display (scroll up/down)?

Or even save, close and reopen the workbook?

Gord Dibben wrote:

To add to this, if you run this macro on the cell with 39542 the value
does not
change in the formula bar but will take the format in the cell.

Sub DateFormat()
Selection.NumberFormat = "dd-mmm-yyyy"
End Sub

Curious.

Gord

On Sat, 5 Jan 2008 16:26:44 -0000, "Sandy Mann"

wrote:

If I enter 39452 in a cell and format it as Accounting I see £39,452.00
in
the cell but still simply 39452 in the formula bar. If I format the
cell as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar.
So I
am not simply seeing in the formula bar the number formatted to look
like a
date by the formatting I applied or the number that I enter

--

Dave Peterson


--

Dave Peterson

Good point Dave.

The scrolling around acted like a "refresh" of the display and the formula bar
changed to a date.

Also F2 + Enter will make the change.

Not so curious now<g


Gord

On Sat, 05 Jan 2008 12:53:11 -0600, Dave Peterson
wrote:

It changed for me in my short tests.

I wonder what happens if you update the display (scroll up/down)?

Or even save, close and reopen the workbook?

Gord Dibben wrote:

To add to this, if you run this macro on the cell with 39542 the value does not
change in the formula bar but will take the format in the cell.

Sub DateFormat()
Selection.NumberFormat = "dd-mmm-yyyy"
End Sub

Curious.

Gord

On Sat, 5 Jan 2008 16:26:44 -0000, "Sandy Mann"
wrote:

If I enter 39452 in a cell and format it as Accounting I see £39,452.00 in
the cell but still simply 39452 in the formula bar. If I format the cell as
a date I see 5 Jan 2008 in the cell and 05/01/2008 in the formula bar. So I
am not simply seeing in the formula bar the number formatted to look like a
date by the formatting I applied or the number that I enter