1.1 changes into dates that changes into 39448??

[MarteinnS]

I cant write 1.1 in Excel 2003. Always when I do that it changes to "1.jan"
Then when i try to format the cell to general, number or anything else it
changes to 39448.
Please tell me how i can write 1.1 in excel without it changing into 39448
or tell me how i can delete date formats out of Excel so I can use it.......
I am using a list of numbers that update automatically from a website, but
it gives all the values out as "1.1" or "11.3" or something like that but
they all change into dates which changes into random date-formated numbers I
cant use.

Also, if anyone knows. How Can I change 1.1 to 1,1(a number i can use for
calculations)

 

[Dave Peterson]

It sounds like your windows format for the short date is using the dot
character. This is pretty unusual. I'd use the control panel to change this
character to a slash or hyphen.

But if you don't want to change this setting, you could preformat the cell as
text, then do the data entry or start with an apostrophe: '1.1

But if you don't change this setting, don't you have trouble typing 1.1 (=11/10)
when you want a real number?

 

[FiluDlidu]

Go in the control panel of your computer and edit your regional settings to
fit what you want them to be is what I would recommend (if your short date
settings are m.d.y, then typing 1.1 will indeed be interpreted as a date by
Excel).

Else, you can set a formula that would take your 39448 and transform it into
1,1 (assuming your 1.1 or 1.jan or 39448 is in cell A1):
=--(MONTH(A1)&"."&DAY(A1))

 

[FiluDlidu]

Of course I meant:

=--(MONTH(A1)&","&DAY(A1))

(changed the dot for a comma, to fit your settings...)

 

[FiluDlidu]

Or maybe it even needs to be backwards, given the fact it gives you 1.jan and
not jan.1...

To find out, try 1.2:

- if it returns 1.feb, then to retrieve the value, use:
=--(DAY(A1)&","&MONTH(A1))

- if it returns 2.jan, then use the first one I gave you:
=--(MONTH(A1)&","&DAY(A1))

 

[MarteinnS]

Thanks! I changed the windows date format to slash. And it is working now.
Pre-Format didn't work as the list i used updates from a website.
1,1 was working fine. it was just 1.1 (one-dot-one).
In addition, Now i can't write 11/10 but =SUM(11/10) works. That's what i
wanted, thanks!

 

[MarteinnS]

Thanks! That helps allot! I changed my regional settings tho and it works
like it should do. I will use that formula allso as i will probably be
sharing this document with my friends. (so they don't get those values in
dates like i did)
Thanks again!

 

[Dave Peterson]

Yep.

11/10 looks way too much like a date.

=11/10
should work ok, too.

Or just 1.1
<vbg>