Excel: Date function doesn't show correct date

[pho01]

Well, it seems to me that some computers, date function is not shown correctly:

On my machine, when I entered:

=Month(Now()) => it shows 1, which is January, is not correct

=day(Now()) => it shows #REF? , which is not correct.

but some computer I checked, it worked alright. All have MS2K installed on the machine....

What is missing that caused the misfunction of the date worksheet functions? Or how to solve it?

Thanks,
Phuong

 

[weetnie]

Have you got your cell format on general?

 

[pho01]

Yes, I sure do.

An interesting this is that:
when I do:

=MONTH(NOW()) =>it gives me '3'
When I do:
=TEXT(MONTH(NOW()),"mmmm&quot => it gives me 'January'

all set to 'General'

 

[combo]

You try to format Jan 3, 1900 (MONTH(NOW()=3, which is that date in date convention).
Use =TEXT(NOW(),"mmmm&quot and all will work.

combo

 

[doldol]

If the cell is corrupt(you can try the function in a far way cell if it works fine), go to Edit -> Clear -> All.
And retype the function.

 

[xlbo]

"=MONTH(NOW()) =>it gives me '3'
When I do:
=TEXT(MONTH(NOW()),"mmmm&quot => it gives me 'January'"

This is not surprising:

Your 1st formula picks up the format of the cell (ie general and therefore shows the month NUMBER as it's supposed to

Your 2nd formula forces the cell to be TEXT by using the TEXT formula and therefore will show exactly what is entered into it. In this case, you have used the TEXT function to format the date before it is entered into the cell (which is forced to be text by the text function) so you see the mmmm format of the month. If you look up date formats in the help file, you will see that there are actually at least 3 seperate ways of showeing the month:
m 1
mmm Jan
mmmm January Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[GlennUK]

Phuong,

in your first posting you said ...

" On my machine, when I entered:

=Month(Now()) => it shows 1, which is January, is not correct "

and then your next posting said ...

"An interesting this is that:
when I do:

=MONTH(NOW()) =>it gives me '3' "

so , which is true????????

Glenn.

 

[pho01]

Glenn,

Yes, i tried in on my machines at work, AND at home. at work, when i typed =Month(Now()), it gives me 1

and at home, when I typed:
=Month(now()), it gives me 3.

It's an interesting fact, but it's true. I'll check the computer date when I get back to my office.
Thank you all.

-Phuong