Checkbook CSV export to XLSX (Office 365): Convert date format to a simple date

[ladyck3]

I have a checkbook app that I use on my android phone which will export to a *csv file.
The exported data lists the date for each transaction as shown below:

Mon Dec 15 16:42:27 CST 2014

How can I convert the date to show without the time stamp, just the date.
I am trying to match transactions with another spreadsheet who shows the dates as follows:

yyyy-mm-dd

My Excel is SO RUSTY.... I've been out of the Excel world for over 8 years and I've lost my mind here.

Suggestions/Recommendations requested.
HELP!
Most Sincerely... and y'all STAY SAFE!
Thank you in advance for any help you might lend....

Laurie

 

[pmonett]

You want to transform

Mon Dec 15 16:42:27 CST 2014

into a yyyy-mm-dd format.

Extracting the day and the year should not be too difficult.

The year you can easily get with @Right(cell;4).
Getting the day is a bit more tricky, but not overly so, and can be caught with @Trim(@Right(@Left(cell;10);2)).

The real problem is the month, and there's only one solution for that. You need to create a table of Jan to Dec and 1 to 12, and use @Index/@Match to convert the text to the month value.
Do you know how to manage that ?

 

[strongm]

Here's one way, using VBA

[blue]    Dim result() As String
    result = Split("Mon Dec 15 16:42:27 CST 2014", " ")
    MsgBox Format((result(5) & " " & result(1) & " " & result(2)), "yyyy-mm-dd")[/blue]

Here, have it as two functions you can use in cells:

[blue]Option Explicit

[COLOR=green]' Expects date as a string in format ddd MMM d hh:mm:ss tz yyyy
' Outputs a string representation of date in format yyyy-MM-dd[/color]
Public Function GetDate(strDate As String) As String
    Dim result() As String
    result = Split(strDate, " ")
    GetDate = Format((result(5) & " " & result(1) & " " & result(2)), "yyyy-mm-dd")
End Function

[COLOR=green]' Expects date as a string in format ddd MMM d hh:mm:ss tz yyyy
' Outputs a date.  How this date is displayed in dependant on cell formatting[/color]
Public Function GetDate2(strDate As String) As Date
    Dim result() As String
    result = Split(strDate, " ")
    GetDate2 = result(5) & " " & result(1) & " " & result(2)
End Function[/blue]

 

[SkipVought]

Laurie,

[tt]
=DATEVALUE(MID(A1,4,7)&", "&RIGHT(A1,4))[/tt]

BTW, the FORMAT of a date in Excel is merely a Display feature. A date like today's date formatted as yyyy/mm/dd or mm/dd/yy are EQUAL to 44440.

Enter 9/1/2021
Select that cell and change the NUMBER FORMAT to General.
Now change the number format to DATE yyyy/mm/dd
Doesn't matter the date format. The underlying VALUE (44440) is what matters!

faq68-5827

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[ladyck3]

Thank you Skip... a few days later, sorry for the delay.
Well.. THANK YOU, rather... that formula worked perfectly and now I can play with the MATCH and INDEX functions. but I'm pretty sure I can figure it out from here. Case Closed.

SUCCESS!!!!!
Thanks for taking things to a basic level for/with me
(The FAQ helped too... of course.)

Laurie

 

[SkipVought]

I just don't need to get cross-stitched with a knit-picker.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein