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

Hi

Thank you for your advice.

I have tried the last one you mentioned.

However, it totally changed the exact date time.

Look like this:

Answer 4

Hi

Thank you for your suggestions.

I have tried the formula that you provided, even I am not very clear about its meaning.

It seems to work.

The date is right, while the time is wrong.

Is there anything wrong with my formulas?

Answer 5

What kind of files are these?

If they are csv or text files, you should IMPORT them, preferably using Power Query.  When you do that, you will have the opportunity to tell Excel how the date column is formatted (i.e: how it exists in the file; not how you want it to be).  Excel will then be able to convert the values to proper dates.