how to return a serial number to a date

[Holly]

Example: 40899

Is there a formula to convert the serial number 40899 above back to
12/22/2011?

Thanks, Holly

 

[T. Valko]

Format the cell as Date

 

[Joe User]

Is there a formula to convert the serial number 40899
above back to 12/22/2011?

It would help to know the context and the purpose.

If 40899 is the value of an Excel cell, you can simply change the format to
Date or some Custom date format.

But note that that only changes the appearance, not the value. That is
usually what we want. But if you want text in the form of a date, if 40899
is in A1, then in B1, enter the formula =TEXT(A1,"mm/dd/yyyy") or whatever
date format you want. Then copy B1, and paste-special-value into A1. You
can then delete B1.

There are similar things to do in VB. If that is your context, post a
response and we can provide details.

 

[Dave Peterson]

If you're seeing 40899 and you can't reformat the cell as a date to show what
you want, then try:

Format the cell as a date
retype the entry in that cell.

If you still see 40899 in the cell, but you see 12/22/2011 in the formula bar,
then you're looking at formulas.

In xl2003 menus:
tools|options|view tab|uncheck formulas

(It's under the Office Button|Excel options (somewhere) in xl2007.)

Or you can use:
ctrl-` (ctrl-backquote (the key to the left of the 1/! on my USA keyboard) to
toggle this setting in any version of excel.

 

[Holly]

Thank you, Joe.

For some reason, I can't Use "Date" to format the column has the serial
number below.

I need to group the data by year.

The suggestion below works for me.

Again, thank you.

Holly

 

[T. Valko]

Off topic...

I made a reply to you in that thread about random non-repeating numbers but
for whatever reason it didn't get posted. So I tried posting it as a reply
to my own reply but that didn't work either. It didn't contain any
references to "X" or "Y" or "Z" either! It hasn't shown up at any of the
usual places: OE as a newsreader, the MS discussion groups website or even
Google Groups.

I don't want to hijack this thread so I guess I'll just wait for the topic
to come up again.

 

[Joe User]

Off topic....

I made a reply to you in that thread about random non-repeating numbers
but for whatever reason it
didn't get posted.

Tried to email you, but it got bounced. I cannot find the right email
address for you.

If you wish, send me the original message that you could not post.
Right-click Forward As Attachment. Send to joeu2004 "at" hotmail.com. I
try to root cause the problem.


----- original message -----

 

[T. Valko]

OK, sent you an email.