Date Format Doesn't Work

[Daniel L. Lieberman]

Vista Business, Office 2007.

On one Vista of my three machines (2 Vista, 1 XP Pro SP2) date formatting
does not work.
I have tried a blank sheet.
I have tried the other 2 machines they work both with blank sheets and with
the a sheet saved on the problem machine.
I have run repair without any improvement.

Still the date will not format. With the Ctrl+; shortcut the date appears as
set in regional settings. This has occurred (and started) within the last
two weeks. (Sorry I don't recall exactly when the problem started.

Any suggestions?

TIA

 

[Dave Peterson]

I'd try formatting the cell as General, then type in the date (ctrl-; if you
want).

You may want to give some examples of what you tried--and what failed.

And just a guess. If you're typing
30/03/2007
and your short date format isn't dmy, then this will be treated as text--not a
date.

 

[Daniel L. Lieberman]

Dave,

I tried your suggestion although I had tried it before. It did not work.
As I explained I have used the Ctrl+:
Since the sheet works on two other machines which do not have this problem
the question is what more can I do than run repair.

Daniel

 

[Dave Peterson]

I don't have any guesses:

You may want to give some examples of what you tried--and what failed.

What do you type?
What do you see in the formula bar?
What do you see in the cell?

If you do the data entry in A1 and put =isnumber(a1) in b1, what do you see?

 

[Daniel L. Lieberman]

Dave,

I type ctrl+; (In Regional Settings I have defined short date as
did-MOM-yy.)
In the formula bar I see 30-03-2007.
In the cell I see the same thing.
The answer to isnumber(a) is FALSE.

Interestingly in regional settings the result of did-MOM-yy appears as
30-Mar-07

I think I will try a removal and reinstall since everything works fine on my
two other machines.

This is a real puzzle to me.

Daniel

 

[Dave Peterson]

How about trying a short date format of: dd-mmm-yy

I'm not sure what did-MOM-yy is supposed to do.

Dave,

I type ctrl+; (In Regional Settings I have defined short date as
did-MOM-yy.)
In the formula bar I see 30-03-2007.
In the cell I see the same thing.
The answer to isnumber(a) is FALSE.

Interestingly in regional settings the result of did-MOM-yy appears as
30-Mar-07

I think I will try a removal and reinstall since everything works fine on my
two other machines.

This is a real puzzle to me.

Daniel

 

[Niek Otten]

<The answer to isnumber(a) is FALSE>

That means your cell was formatted as TEXT before you entered CTRL+;
Format as General and re-enter the date or the shortcut.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Dave,
|
| I type ctrl+; (In Regional Settings I have defined short date as
| did-MOM-yy.)
| In the formula bar I see 30-03-2007.
| In the cell I see the same thing.
| The answer to isnumber(a) is FALSE.
|
| Interestingly in regional settings the result of did-MOM-yy appears as
| 30-Mar-07
|
| I think I will try a removal and reinstall since everything works fine on my
| two other machines.
|
| This is a real puzzle to me.
|
| Daniel
|
|
|
| | >I don't have any guesses:
| >
| >> You may want to give some examples of what you tried--and what failed.
| >
| > What do you type?
| > What do you see in the formula bar?
| > What do you see in the cell?
| >
| > If you do the data entry in A1 and put =isnumber(a1) in b1, what do you
| > see?
| >
| >
| >
| >
| > "Daniel L. Lieberman" wrote:
| >>
| >> Dave,
| >>
| >> I tried your suggestion although I had tried it before. It did not work.
| >> As I explained I have used the Ctrl+:
| >> Since the sheet works on two other machines which do not have this
| >> problem
| >> the question is what more can I do than run repair.
| >>
| >> Daniel
| >>
| >> | >> > I'd try formatting the cell as General, then type in the date (ctrl-;
| >> > if
| >> > you
| >> > want).
| >> >
| >> > You may want to give some examples of what you tried--and what failed.
| >> >
| >> > And just a guess. If you're typing
| >> > 30/03/2007
| >> > and your short date format isn't dmy, then this will be treated as
| >> > text--not a
| >> > date.
| >> >
| >> > "Daniel L. Lieberman" wrote:
| >> >>
| >> >> Vista Business, Office 2007.
| >> >>
| >> >> On one Vista of my three machines (2 Vista, 1 XP Pro SP2) date
| >> >> formatting
| >> >> does not work.
| >> >> I have tried a blank sheet.
| >> >> I have tried the other 2 machines they work both with blank sheets and
| >> >> with
| >> >> the a sheet saved on the problem machine.
| >> >> I have run repair without any improvement.
| >> >>
| >> >> Still the date will not format. With the Ctrl+; shortcut the date
| >> >> appears
| >> >> as
| >> >> set in regional settings. This has occurred (and started) within the
| >> >> last
| >> >> two weeks. (Sorry I don't recall exactly when the problem started.
| >> >>
| >> >> Any suggestions?
| >> >>
| >> >> TIA
| >> >
| >> > --
| >> >
| >> > Dave Peterson
| >
| > --
| >
| > Dave Peterson
|

 

[Daniel L. Lieberman]

Dave,

I used the dd-MOM-by. I don't know how I typed MOM the "ME" and "OF" aren't
even close to each other. I will try the lower case "me" now.

Now I know when I spell checked the autocorrect changed it to MOM.

Daniel

 

[Daniel L. Lieberman]

Dave,

I tried mmm instead of MMM and it was automatically changed to MMM.
This is really weird because in Regional it shows as 30-Mar-2007 which is
not what it should do. A dd-MMM-by in Regional settings should give
30-Mar-07. What the Ctrl+; gives is 30-03-2007. That is really funny.

Daniel

Dave,

I used the dd-MOM-by. I don't know how I typed MOM the "ME" and "OF"
aren't even close to each other. I will try the lower case "me" now.

Now I know when I spell checked the autocorrect changed it to MOM.

Daniel

 

[Daniel L. Lieberman]

Dave,

The bug is somewhere in Vista. I went into Regional settings and changed to
UK English which uses day Month Year and everything works fine in Excel. I
just changed the pound sign in currency there to $.

I guess there aren't too many ex-military programmers who checked this out.

Daniel

Dave,

I tried mmm instead of MMM and it was automatically changed to MMM.
This is really weird because in Regional it shows as 30-Mar-2007 which is
not what it should do. A dd-MMM-by in Regional settings should give
30-Mar-07. What the Ctrl+; gives is 30-03-2007. That is really funny.

Daniel

 

[Dave Peterson]

Glad you found a solution/work-around.

Just a note about the MMM vs mmm stuff. In my experience, there is no
difference between them--mmm and MMM both behave the same way. (But I haven't
used Vista very much <bg>.)

Dave,

The bug is somewhere in Vista. I went into Regional settings and changed to
UK English which uses day Month Year and everything works fine in Excel. I
just changed the pound sign in currency there to $.

I guess there aren't too many ex-military programmers who checked this out.

Daniel

Dave,

I tried mmm instead of MMM and it was automatically changed to MMM.
This is really weird because in Regional it shows as 30-Mar-2007 which is
not what it should do. A dd-MMM-by in Regional settings should give
30-Mar-07. What the Ctrl+; gives is 30-03-2007. That is really funny.

Daniel