Changing the date in a cell

[Mark]

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 Phillips]

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]

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.

 

[Sandy Mann]

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

--


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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

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

 

[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.

 

[Sandy Mann]

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:

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[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???

 

[Stan Brown]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

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

 

[Gord Dibben]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

As you said, dates are funny things

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[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?

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[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.

 

[Gord Dibben]

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