Need to convert "Fri Feb 23 2018 00:10:12 GMT+0100" to a usable Date and time

[davidkruyt]

Good day
Please can someone assist with a formula to convert a list of dates in this format "Fri Feb 23 2018 00:10:12 GMT+0100" to a usable Date and time that can be use in a excel pivot table

[shukla.ankur281190]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[kersplash]

Can you give more sample data?

Attach a workbook with the sample data.

[samba_ravi]

If you have the above data in A1
use below formula to convert it in to data
=(MID(MID(A1,5,20),5,2)&REPLACE(MID(A1,5,20),5,2,))+0

[Phuocam]

Or try:

=--MID(REPLACE(A1,11,0,","),5,21)

[FDibbins]

That may even be a real date/time combo, cant say for sure without seeing some sample data

[davidkruyt]

Many thanks for your replay

I have attached a section of the file Ima working with.
The date formula I am currently using is in row "J"

Would really appreciate if i could get the time to display correctly as well.

Many thanks

[kersplash]

Did you try the suggestions given already?

[FDibbins]

You were almost there...
=DATEVALUE(MID(A2,9,2)& "-" & MID(A2,5,3) & "-" & MID(A2,12,4))+--MID(A2,17,8)

[FDibbins]

This will also work...
=--(MID(A2,9,2)& "-" & MID(A2,5,3) & "-" & MID(A2,12,4))+--MID(A2,17,8)

[davidkruyt]

Thank you all. Problem solved. The format I was using did not show the "seconds"

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

Thanks for the feedback