# get date format from a number

• Hello experts

I am trying to convert the numbers in the column D to date format. I have to use 5 help columns to get the date in the right date format as shown in the 6th help column. How Is it possible convert the number into date with a single formula compatible for 2007 version of excel.?

Shared the query on other forum as well.

## Files

Edited once, last by RAJESH SHAH ().

• Thanks pecoflyer. It worked like a piece of cake.

At lease now I know I am posting the queries correctly as none of my other posts are replied.

• At lease now I know I am posting the queries correctly as none of my other posts are replied.

If so, you are probably not explaining clearly what you are trying to achieve in those other posts

• FWIW you can also use:

=TEXT(D10,"0000-00-00")+0

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Pecoflyer. I concur. It is difficult for me to explain my query. I always try and explain as simple as possible.

• FWIW you can also use:

=TEXT(D10,"0000-00-00")+0

That too worked. Thanks rory. Feeling good to learn new and different way to write a formula and get the result. Thanks

• For the record, I came across an inbuilt function in excel where the date like numbers can be converted into date without any formula.

Select your cells you wish to convert, click text to columns, which will open a wizard:

Step 1: Select delimited
Step 2: uncheck all delimiter options
Step 3: select date option, and YMD from the drop down.

Finish.

This will replace the numbers with the date format.

• I know, but you asked for a formula, so...

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Guys, Thank you for the formula. I know I asked for a formula. I shared it because it was something new I learnt and just wanted to share it with everyone. No offence please.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!