How to convert a String date to a Date

Question

How can I convert this string:

Tue Jan 24 14:59:20 BRT 2017.

Into a date that includes day month year and time and timezone, using Excel functions only.

I have several cells with dates following this format. I have to compute the difference between some of these dates in minutes. I believe that the first step is converting the date to a String to a real date information. Then, I will be able to: order the dates and compute the time between consecutive dates.

...Doesn't the string already have that? Can you post an example of the expected output? Will all the strings have that exact format? More examples would help. — BruceWayne
– BruceWayne, Commented Sep 19, 2017 at 14:33
Possible duplicate of Format code for a date with time zone in excel — Mohamed Bathaoui
– Mohamed Bathaoui, Commented Sep 19, 2017 at 14:35
How can i break the String and collect the date parts that i need? — user3444287
– user3444287, Commented Sep 19, 2017 at 14:42
You will need to lose the time zone information, and as such if you are comparing all the date/times over multiple time zones you will also need to convert them to a standard time zone to do the math. If it is all in the same time zone then this is simpler. If in various you will need a table that tells excel how much time to add or subtract to get to a standard. — Scott Craner
– Scott Craner, Commented Sep 19, 2017 at 14:46

Scott Craner · Accepted Answer · 2017-09-19 14:50:56Z

1

Use this formula:

=--(SUBSTITUTE(MID(A1,5,LEN(A1)),"BRT",""))

Then format it to the format you want.

It will now work in math equations.

answered Sep 19, 2017 at 14:50

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BruceWayne Over a year ago

(@user3444287, If the timezone does change, you could also add a MID() to pull out "BRT" instead of putting it in there directly.)

user3444287 Over a year ago

i was able to remove the timezone using notepad++, but i still cannot convert to a date dropbox.com/s/44q0kfxc2ivnqy1/Sem%20t%C3%ADtulo.png?dl=0

Scott Craner Over a year ago

did you try my formula on the original data?

user3444287 Over a year ago

it not worked, but i think i did otherway using string manipulation dropbox.com/s/44q0kfxc2ivnqy1/Sem%20t%C3%ADtulo.png?dl=0

Collectives™ on Stack Overflow

How to convert a String date to a Date

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related