How to make excel understand custom format dates?

[Radecki]

Hi All,

I get dates from another tool in a custom format and Excel does not recognize it as dates, here is an example:

Sep 10, 2023, 5:51:06 AM

Sep 10, 2023, 1:01:21 AM

Sep 9, 2023, 2:32:44 PM

Sep 8, 2023, 10:09:29 PM

The other tool will not allow to change the date format on the output, hence the conversion needs to happen in Excel.
I only need month, day and a year. The time can be removed.

Any idea how to convert it into a format that Excel would understand?
Please help, I tried with Datevalue function and Text to column option but both failed for me (but I may be doing it wrong).

Thanks!

 

[denzo36]

Power Query will remove these easily for you.

1. Import your dates into Power Query
2. Then go 'Add Column' -> Date -> Parse
3. Rename your new column as desired and remove the old column if needed
4. Load the corrected dates back into your sheet

 

[awoohaw]

in excel, try:
for excel 365. Please update your excel version, as this may not work for you.

=DATEVALUE(TEXTBEFORE(A2,",",2))+TIMEVALUE(TEXTAFTER(A2,", ",2))

 

[Georgiboy]

If using an older version of Excel then you could use a small UDF:

Function DateFix(str As String)
    Dim var As Variant
    
    var = Split(Replace(str, ",", ""), " ")
    DateFix = CDate(Join(Array(var(0), var(1), var(2)), " "))
End Function

Used in the spreadsheet like this:

=DateFix(A1)

 

[awoohaw]

Here are the 365 and non 365 worksheet formula versions. (I'm sure there are many ways to do this):
You can do the date formatting.

Mr excel questions 66.xlsm
	A	B	C
1		365	non 365
2	Sep 10, 2023, 5:51:06 AM	45179.24382	45179.24382
3	Sep 10, 2023, 1:01:21 AM	45179.0426	45179.0426
4	Sep 9, 2023, 2:32:44 PM	45178.60606	45178.60606
5	Sep 8, 2023, 10:09:29 PM	45177.92325	45177.92325
Radecki

Cell Formulas
Range	Formula
B2:B5	B2	=DATEVALUE(TEXTBEFORE(A2,",",2))+TIMEVALUE(TEXTAFTER(A2,", ",2))
C2:C5	C2	=DATE(MID(A2,FIND(", ",A2)+2,4), MATCH(MID(A2,1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0), MID(A2,FIND(" ",A2)+1,FIND(",",A2)-FIND(" ",A2)-1) ) +TIMEVALUE(TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(":",A2)-3),"")))

 

[Radecki]

Thank you @awoohaw, I was looking for a function to resolve this issue (rather than power query or VBA). I have a 365 Excel and somehow the datevalue function didn't work but the non 365 function (date) did the trick, thank you!

Thank you all!

 

[awoohaw]

Thank you @awoohaw, I was looking for a function to resolve this issue (rather than power query or VBA). I have a 365 Excel and somehow the datevalue function didn't work but the non 365 function (date) did the trick, thank you!

Thank you all!

Strange. Did you try to debug it any? Are there extra characters in your text dates that isn't in what I used? Anyway, you found a solution here.

Best wishes!

 

[Dave Patton]

check your system's Regional Settings for dates

See Excel's help

DateValue will work if the data's date and systems date have the same Regional Settings.

Dates Time.xlsm
	A			D
1
2	Sep 8, 2023, 10:09:29 PM			08-Sep-23 22:09:29
3	Sep 10, 2023, 5:51:06 AM			10-Sep-23 05:51:06
4
5	Sep 8, 2023, 10:09:29 PM			08-Sep-23 22:09:29
6	Sep 10, 2023, 5:51:06 AM			10-Sep-23 05:51:06
7
3f

Cell Formulas
Range	Formula
D2:D3,D5:D6	D2	=DATE(MID(A2,FIND(",",A2,1)+2,4),MONTH(1&LEFT(A2,3)),MID(A2,FIND(" ",A2)+1,FIND(",",A2)-FIND(" ",A2)-1))+TEXTAFTER(A2," ",3)