Date time appears as "Tue Nov 09 2021 20:27:51 GMT-0500 (Eastern Standard Time) How to convert to a value that I can use to calculate time difference between two dates?
CodePudding user response:
Do you want to show the difference in time(hours) or days? For days you can use a column with the following formula
=CONCAT(MID(A3,9,2),"-",MID(A3,5,3),"-",MID(A3,12,4))
where A3 is your cell containing the string "Tue Nov 09 2021 20:27:51 GMT-0500 (Eastern Standard Time)". This will be converted to 09-Nov-2021. You can then use a normal subtraction (A-B) to calculate the day difference between 2 dates.
CodePudding user response:
Use MID:
=MID(REPLACE(A1,11,0,","),5,21)-TIME(5,0,0)
Then format it how you would like.