US date & time to UK date & time - help!?!

[iantix]

Hi all,

Have found solutions using text to columns for converting US date format to UK date format.

All good, expect my US source has time as well as date and I need it to be in UK format.

I cannot find a solution that does that with the time in the mix - can anyone assist please?

Attached is a sheet illustrating source format and desired output - would love the 3rd How column to have a solution in if possible please?

Tried a few LEFT/RIGHT solutions but that formats got all screwed up.

Can anyone assist please?

Ian

[6StringJazzer]

You show the time as 12:00:54 PM and want it to look like 0:00. This is incorrect. Your time is 54 seconds past noon so should look the same in UK time. I modified your first entry to to 12:00:54 AM to show midnight, which will show as 0:00.

What you want to do is done trivially by custom format of "d/m/yyyy h:mm". Please see attached.

[iantix]

Thanks - apologies, yes - I took the wrong desired outcome on my initial sheet.
The source is from Facebook, but your third column solution is still in US format - month/day/year - desired outcome is day/month/year.
Doing that AND the time, is foxing me.
Original sheet reattached.
Ian

[Pauleyb]

6StringJazzers spreadsheet looks correct to me.

Realize that Excel will generally use the date appropriate for the locale. Here is the website.
https://support.office.com/en-us/art...a-b4d30c528309

Note the text which says:
Note: Date and time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

I believe that is the default. So, you may not need to change the date, unless you expressly wish to show both formats.

[iantix]

Thanks - okay so have attached a screen cap of what his sheet looks like when I open it - this is why I'm asking on here as the formatting solution isn't working for me, neither does the one shown.
The output is from facebook in column A.
It needs to show as per column B (ignore the noon/midnight issue - the dd/mm/yy bit with time also added is what's tricky)

[6StringJazzer]

Now, I think you have a bigger problem. What is your US source? If your source is giving you date values in Excel then the formatting should be totally transparent to you and you would never even notice. But I suspect you are getting text strings. You have presented them as Excel date/time to illustrate your question, which unfortunately caused all the confusion which I am about to explain.

This is what it looks like when I open it, and if you look at your "Desired" column you will see why I provided the solution I did.

times.JPG

The problem with your file is that you put in fake dates to force the display to be a certain way, so I can't tell what the real dates are. For example, in column A you used June 10 and in column B you used October 6. You have UK localization in your Excel setup, so I see a different display than you do.

The custom format of "d/m/yyyy h:mm" that I've already provided will show the UK version for date/time. But I don't think that's going to solve your problem.

[iantix]

Let's break it down, looking at row 2 only.

We get, as a text string, from Facebook (said before) the following; 10/06/2017 12:00:54 (translated as October 6th 2017 at 12:00:54pm - US format on month and day order, but in text string format)

I need it to show in date format as; 06/10/2017 12:00:54 (translated as 6th October 2017 at 12:00:54pm - UK format on month and day order and in date/time format)

I know this needs a formula.

Anyone?

Am struggling with splitting out date portion only using a combo of MID/LEFT etc and DATE but getting nowhere yet.

Thanks,

Ian

[sandy666]

maybe use custom format on original : mm/dd/yyyy hh:mm:ss

[Pauleyb]

If these are truly coming in as text strings as you show (i.e. using 2 chars for month, 2 chars for day, and 4 chars for year) then you can use this equation:
=DATE(MID(E9,7,4),LEFT(E9,2),MID(E9,4,2))
Assuming the text is in cell E9. This will then display the date per the local settings.

[FDibbins]

I just tried Pauleyb's formula and it worked as expected. Your regional date settings *should* then display the date as needed, but if not, just format the answer to what you need

[iantix]

It's so odd - even with PauleyB's formula, I get;

04/09/5009 00:00

From this; 10/06/2017 12:00:54

And that's with the cell formatted to dd/mm/yyyy hh:mm

I'm gonna have to retype all the dates for now, as this isn't working however I try it, unless anyone has another suggestion.

[sandy666]

You've (probably) UK regional settings (dd/mm/yyyy) so you can set M/D/Y (because source is in US style) via text2columns and excel should translate dates from US to UK automatically. Then set custom format for date & time: dd/mm/yyyy hh:mm:ss
It works for me with your example

[iantix]

Thanks - does text to columns work when time is also present? I'll give it a go

[sandy666]

Try it, you can use Ctrl+Z if it doesn't work properly for you

[iantix]

And year 5009? Really?

[iantix]

Okay, T2C has worked, unsure why it wasn't before but thanks Sandy666 for getting me over the line and to the others for the earlier assist. Had to concat the date and time values back into one but that was easy.

[sandy666]

here is example with two solutions (first is visual only not real)