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

[Tony_Curn]

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 format. i.e. 4-May  I have tried many of the different number formats available, but can't get close to the original format. For instance Number format changes 4-May to 41763.00. Any ideas?

[#2]

@Tony_Curn 

I'm on a Mac and when I choose to format a date I can select the region "Alsatian (France)". It will show the decimal format as one of the standard options. If you don't have these you might try a custom format "d.m;@". I've attached some screen shots that show you what I mean.

[#3]

Hello,

If I get your question accurately, you want to change date from decimal to proper date format. If that's what you meant, kindly execute CTRL + 1 on the date column of your data.

In the Format Cell dialogue box, you can select desired date format

[#4]

Hi, @Abiola1 

I don't think @Tony_Curn had this in mind.

It's that somebody sends you an excel file, let's say with mater data. On original computer, the data is in decimal values.

I receive it via email. I open it on my computer. And certain decimal values are ''automatically'' changed to arbitrary dates. Now, if I format it back to numbers, the numbers are not the same anymore. 

e.g: 1.5 ---> May first (or June 5th, depends on the will of excel at that time), and then convert back to number, you get something like 412381. 

1.5 =! 412381

That's the problem here.

[#5]

@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")

[#6]

Thank you @Peter Bartholomew 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!