how to convert date into number in excel

Question

Hi 

I would like to covert the date into number, but it does not work.

I have tried to format the cells, but it does not work as well.

Here is my cells.

What's wrong with it? and how I can covert the date into number?

Thanks!

Answer 1

Given the way your data appears, I will make the following assumptions.

• The data originally came from a CSV file with the date formatted as "D/M/YYYY H:MM:SS AM/PM"
• Your "Windows Regional Short Date Settings" are "MDY"
• Your cell format in column C of your Excel file is "yyyy/mm/dd hh:mm:ss am/pm"

If all that is true, you have two problems

1. The dates that you see as text need to be converted to "real" dates.
2. The dates that you see as dates were likely converted incorrectly with the month and day having been interchanged from what they were originally intended.

To fix both problems, you need to

• Determine if the value to be converted is a date or a text string
  • You can use ISNUMBER for that since dates are stored as numbers
• If it is a date
  • Reverse the month and day parameters
• If it is a text string
  • Use text functions to parse out the relevant values
• I recommend using the DATE function as there is no ambiguity or issues with windows regional settings to muck things up.

=IF(ISNUMBER(C1),

           DATE(YEAR(C1),DAY(C1),MONTH(C1))+TIME(HOUR(C1),MINUTE(C1),SECOND(C1)),

           DATE(MID(C1,FIND("/",C1,FIND("/",C1)+1)+1,4),MID(C1, FIND("/",C1)+1, FIND("/",C1,FIND("/",C1)+1)-FIND("/",C1)-1),LEFT(C1,FIND("/",C1)-1)) +

                         TIMEVALUE(MID(C1,FIND(" ",C1)+1,12)))

Then it is just a matter of formatting to return either a date or a number.

Answer 2

So the previous formula does only return the date not the time.  Use this to get both instead of the one above:

=DATEVALUE(TEXT(DATE(MID(C1,6,4),MID(C1,4,1),LEFT(C1,2)),"YYYY/MM/DD"))+TIMEVALUE(TEXT(RIGHT(C1,10),"hh:mm:ss AM/PM"))

This formula is set to reference cell C1.  Adjust the C1 references as necessary to match your first row before dragging down or copying to cells below.

Also, as before if the number of digits in the month, day, or hour changes (1 to 2 or 2 to 1), the start number and the number of characters in the MID functions will have to be adjusted accordingly, and in the LEFT function for the day or the RIGHT function for the time (10 characters to 11) the number of characters would have to be adjusted accordingly.

You may want to paste values over the formula once entered to remove the formula.  As you did above, format to number to see the number value for the date or format as a date in any style you want.

You said that you weren't clear about how the formula worked.  It will be important to figure that out since there will be adjustments to make based on the characters in the data.  Check out Excel help on the MID, RIGHT, and LEFT FUNCTIONS and it will show you which elements in the functions are the start numbers or the number of characters in the functions.  The three sections of the DATE function in order are Year, Month, and Day.

Reply again if you additional questions.  Please mark this reply as answered if this solves your question.

Rich~M

Answer 3

Assuming the cell in Column C is General, format the cells as Number.

Answer 4

Hi

Thank you for your reply.

However, it does not work.

Whatever I change column C cell format into Number/Time/Text/Currency etc., the content in the column cell does not change at all.

I don't know what's wrong with this data. :(

Answer 5

Hi

Some cells in column C alligned to the left are "text values " 

So they are not real excel date values 

This means you have to clean up the column using either "Text to column"  or "Flash fill" 

You could also try 

1. In an empty cell, enter number 1 2) then copy the cell 
2. Select the entire range in column C 
3. and paste special and select

"multiply"

I hope this helps you 

Regards 

Jeovany