Forum Discussion

Copper Contributor

Nov 03, 2019

How to change a date format back to a decimal in excel?

I opened an old excel spreadsheet populated with many data entries that were originally in a decimal format with 1 decimal place e.g 4.5 but I found that they have all been converted to a d-mmm date...

excel

PeterBartholomew1

Silver Contributor

Nov 19, 2021

Tony_Curn

The process has some things in common with unscrambling eggs!

Once you identify that Excel has misread the data input, you can take steps to reverse the process. The day and month can be separated using

DAY(data)
MONTH(data)/10

and then treated as the integer and decimal parts of a number using the appropriate formula

= DAY(data)+MONTH(data)/10
or
= DAY(data)+MONTH(data)/10^LEN(MONTH(data))

the later dealing with months Oct-Dec.

Another solution that could work given computer settings other than those that messed up the data in the first place would be

=--TEXT(data,"d.m")

John225

Copper Contributor

Nov 19, 2021

Thank you PeterBartholomew1 for your reply. This has actually worked. In some cases I needed to do some additional magic, but it would not have worked without your initial input.

Not to mention the burst of laughter brought on by your 'unscrambling eggs' comparison, which is the most precise way to describe this problem.

Thank you again!