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 reply.

This file is downloaded from my assignment.

I think it is one of my tasks to deal with it.

I am assigned to match two tables. and their link is only this date column.

In table 1, it contains structured date-time, which can be converted into values.

But in table 2, the date column contains a mixture of text values and number values, just look like the screenshot that I posted in my question.

Answer 4

I'm not sure of your Language & Region settings, but the order of the entries in the cells producing the error differs from that of the cells which produce the expected result & don't conform to the required order for many regions including US. E.g.:

Cell C16 is entered as: 22/5/2020 11:53:17 AM (dd/mm/yyyy...)

But it should be: 5/22/2020 11:53:17 AM (mm/dd/yyyy...)

The function cannot parse the data because there is no 22nd month.

Answer 5

Thanks for posting your question.  I am an Excel user like you.

You can change the mis-ordered dates to a date value with this formula:

=DATE(MID(A1,6,4),MID(A1,4,1),LEFT(A1,2))

This will work on the examples above, however if there is another date with a single digit day, a two digit month or a combination of these the formula would need to be adjusted to reflect the correct start number in the MID functions and/or the correct number of digits in the LEFT function for each combination.

You could do this in another column and then paste values over your formula and copy the formula results back to the original column to replace the mis-ordered entry.

Rich~M