How to fix numbers (eg: 12.06; 30.11; 28.05; etc...) into numbers

Oscar 20

Numbers that are in the format 12.06; 11.03; 30.12; etc... gets turned into dates, but when I try correct it instead of showing 12.06 it shows 45455. I´m unsure why the values change to such high numbers.
How can I fix this issue?

Olaf Helper 41,006 Reputation points

2024-04-29T08:10:57.23+00:00

Which culture setting do you have?

45455 is the numeric value for the date 12.06.2024.
Barry Schwarz 2,191 Reputation points

2024-04-29T08:31:22.2933333+00:00

45455 is the internal representation of 6 June 2024. Your system is obviously set up for ddmm.

The question is why are values like 12.06 being interpreted as dates. How were these values input into the cells? What is the format applied to the cells?

Sometimes, cells which are not originally treated as dates are converted when the cell is double clicked.
Oscar 20 Reputation points

2024-04-29T09:33:22.9333333+00:00

Currently it´s on German day and month (written). And the current category is general, except on these specific cells where it´s custom, when I switch it to general the number goes to 45455.

I would like excel to not see the values as dates but just numbers of the value that I write in not 45455.
Viorel 112.8K Reputation points

2024-04-29T10:13:33.5833333+00:00

You can enter '12.06, however it is usually inconvenient.

Or set the cell or column format to Text before entering numbers.
Oscar 20 Reputation points

2024-04-29T10:29:35.4933333+00:00

Unfortunately it´s a sheet that I´ve been given to work with, and the setting the cell format doesn´t work
Viorel 112.8K Reputation points

2024-04-29T11:14:00.2+00:00

Probably you did not follow the suggestions.

Accepted answer

Emi Zhang-MSFT 22,161 Reputation points Microsoft Vendor

2024-04-30T00:57:33.56+00:00

Hi,

Excel automatically recognizes a date with 12.06 digits, so when you convert it to a number, it will display a date converted number. You can use the formula to add a new auxiliary column and enter the formula to get the number you need:

=NUMBERVALUE(DAY(A1))+NUMBERVALUE(MONTH(A1))*0.01

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Oscar 20 Reputation points

2024-04-30T07:28:21.77+00:00

This helped a lot, and I managed to get the values that I desired. I also realised the problem was with the computer settings for the date, and changed the computer date format which fixed the issue in excel.
Sign in to comment

2 additional answers

Olaf Helper 41,006 Reputation points

2024-04-29T09:45:02.07+00:00

Currently it´s on German

Same here. In German format the comma is the decimal separator, not the dot

12.06 => 12,06
Please sign in to rate this answer.
Oscar 20 Reputation points

2024-04-29T10:31:08.4933333+00:00

The data that I was given is in this format and when I try to use the comma instead of the dot the value turns into 45455..
Sign in to comment
Andreas Killer 80 Reputation points

2024-04-29T15:50:04.22+00:00

Format the cells as text before you write "12.6" into the cell, that prevents Excel from looking into the Region settings and convert the text into a date.

There is no way to get "12.6" back after the conversation is done.

Andreas.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

How to fix numbers (eg: 12.06; 30.11; 28.05; etc...) into numbers

2 additional answers