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

Question

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 47,526 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 3,746 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 122.9K 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 122.9K Reputation points

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

Probably you did not follow the suggestions.

Accepted answer

2 additional answers

Your answer

Olaf Helper 47,526 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 3,746 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 122.9K 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 122.9K Reputation points

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

Probably you did not follow the suggestions.

Answer 1

Emi Zhang-MSFT 30,056 Microsoft External Staff

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

User's image

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.

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.

Answer 2

Olaf Helper 47,526

Currently it´s on German

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

12.06 => 12,06

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..

Answer 3

Andreas Killer 85 Volunteer Moderator

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.

Share via

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

2 additional answers

Your answer