Using Unix US Formatted Date-time-year for calculations in Excel-CodePudding

I have dates that are formatting using the default time formats for Bash (echo $date) for example:

Fri Dec 24 07:35:41 EST 2021

I cannot change this behavior as I don't have permissions to alter our IT solution. I need to be able to load these dates into a UK localised Excel, but any attempts to use the usual Text To Columns DMY approach doesnt work due to the Day, time zone, time etc.

Is the only way to extract the data and re-assemble it or is there a simpler solution?

Edit - why the downvotes? this sites really unwelcoming...

CodePudding user response：

As far as I can see, if we get rid of the EST and the week day FRI, we can get a valid date value. Note: If you want the weekday, you can use the function WEEKDAY() to get the value 6, which is Friday. (1 is Sunday).

So, to get rid of the weekday, you can use the RIGHT() function. All weekdays are 3 letters and a space, so we need to get rid of the 4 first characters, like so:

=RIGHT(A1;LEN(A1)-4)

You can put this just underneath your example time, so place this in cell A2.

To remove the EST is a bit more tricky, we need to know where in the text EST is. For this, we use FIND():

=FIND("EST ";A2)

Now we know at which position the text "EST " appears in A2.

Then we can use a simple REPLACE() function, to replace "EST " with nothing. Like so:

=REPLACE(A2;FIND("EST ";A2);4;"")

Note: The 4 refers to the length of "EST ".

This worked for this example, so you can try it out with different data. Just comment below if it isn't working correctly, and with what data and I'll look into it.

Put a date in A1
Put =RIGHT(A1;LEN(A1)-4) in A2
Put =REPLACE(A2;FIND("EST ";A2);4;"") in A3

You could also merge it into 1 cell:

=REPLACE(RIGHT(A1;LEN(A1)-4);FIND("EST ";RIGHT(A1;LEN(A1)-4));4;"")

You could then place this next to the date in A1, so in B1, and drag this formula down for ALL your imported dates. Now it should work for all of your dates.

You could also copy their values and replace the original dates, then remove the formulas, and you will have the same layout and functionality you were expecting from the beginning!

Cheers! :)

CodePudding user response：

Try this function . write it to module and call from any cell . Like =makeDateFromStr(A1)

Function makeDateFromStr(ByVal someDate As String) As Date
 On Error Resume Next
    Dim var As Variant, Mu As Long
    var = Split(someDate, " ") ' array to hold all parts of the string-date
    Mu = (InStr("JanFebMarAprMayJunJulAugSepOctNovDec", var(1))   2) / 3 ' a way to make short string month (Dec) to number
    makeDateFromStr = DateSerial(var(5), Mu, var(2)) & " " & TimeValue(var(3)) ' putting all together
 On Error GoTo 0
End Function