Excel - How to convert US dates and times to UK dates and times

Anonymous
2021-05-14T17:03:30+00:00

Hello

I wonder if I could receive help with the following:

I have a long Excel spreadsheet where the dates and times are in US format and need to be changed to UK format. The problem is that the date and time need to be merged into one cell.

BEFORE HOW IT SHOULD LOOK
06/03/2021 06:PM 23/06/2021 18:00

Sometimes the data is laid out as below:

DATE TIME HOW IT SHOULD LOOK
06/03/21 06:PM 23/06/2021 18:00

Thanks for your help.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2021-05-14T21:28:56+00:00

That means that the values aren't real dates, but text values that look like dates.

  1. For a range with separate dates in a column:
  • Select the range.
  • On the Data tab of the ribbon, click Text to Columns
  • Click Next> twice.
  • In step 3 of the Text to Columns Wizard, select Date, and select MDY from the drop down.
  • Click OK.
  1. For a range with separate times in a column:
  • Select an empty cell and copy it.
  • Select the range with times.
  • Click the lower half of the Paste button and select Paste Special...
  • Select Add, then click OK.
  1. For a range with dates+times in a column:
  • Select the range.
  • Run the macro listed below.
  • You can discard the macro afterwards.

Sub Text2Date()
    Dim rng As Range
    Dim s As String
    Dim p As Long
    Dim d As String
    Dim t As String
    Dim a() As String
    Application.ScreenUpdating = False
    For Each rng In Selection
        s = rng.Text
        p = InStr(s, " ")
        d = Left(s, p - 1)
        a = Split(d, "/")
        t = Trim(Mid(s, p + 1))
        rng.Value = DateSerial(a(2), a(0), a(1)) + TimeValue(t)
    Next rng
    Selection.NumberFormat = "dd/mm/yyyy hh:nn"
    Application.ScreenUpdating = True
End Sub

Was this answer helpful?

30+ people found this answer helpful.
0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2021-05-16T08:48:31+00:00

I don't use PowerQuery much, so if the following is off, I hope that others will take me to task - criticism is welcome!

Try this:

Click in the column with dates-as-text.

On the Insert tab of the Ribbon, click Table.

Make sure that 'My table has headers' is ticked, then click OK.

On the Data tab of the ribbon, in the Get & Transform group, click 'From Table/Range'.

PowerQuery will automatically recognize the values as date/time and display them in your system date/time format (mine is yyyy-mm-dd hh:mm:ss)

Click Close & Load.

The result is a new table with 'real' date/time values:

You can now apply the desired format:

Was this answer helpful?

10+ people found this answer helpful.
0 comments

45 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2021-05-16T20:34:58+00:00

    As I suspected, Excel HAS recognized some of the dates as real dates.

    The original data source most likely is a CSV file (or some kind of text file exported from another program) which had the original data stored in US format (MDY).

    Someone OPEN'd the file in Excel running on a computer where the Windows Regional settings was DMY.

    What happens in that situation is that dates where the original D > 12 are seen as Text

    If  D<=12, the date string is interpreted incorrectly by Excel as being a date in DMY format.

    By far, the simplest solution will be to return to the original source, and IMPORT the data correctly, telling Excel that the data is in MDY format, so it will know how to parse the date strings.

    Is that possible?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-05-16T20:11:31+00:00

    Unfortunately, I cannot send out the workbook. I'm going to reproduce a small segment of it and forward it.

    We do not want or need the full workbook.

    .

    Obviously you need to protect sensitive data like customer identification or business "Secret" data. 

    .

    When creating example data I like to simplify it.  Instead of using "real" random values for currency amounts or transaction counts I prefer to replace them with simple amount $1.00, $2.00 , counts 10, 20, 100 etc.  For me, the point is to make mental calculations easier to verify the results faster.  For example:

    Cust#  date             Quantity     Price

    1         Jan 1, 2020   1                $1.00

    1         Jan 2, 2020    2               $2.00

    2       Jan 1, 2020     30              $3.00

    2       Jan 1, 2020     20              $2.00

    is simpler to deal with mentally than "real" data like:

    Cust#  date             Quantity     Price

    1234   Jan 1, 2020   123,456   $51.13

    1234   Jan 2 2020      55,013   $123.45

    4557   Jan 1, 2020    131,313  $4.23

    4557   Jan 1, 2020          127   $1,341.53

    .

    Too much data is distracting.  We just need "representative samples" of the various data you encounter.  We don't need many repetitions, just 2 or 3 rows of repeating info, ie 2 or 3 transactions for each of 2 or 3 similar "type" customers.   For example, I identified several text format date/time columns. We don't need them all, Just give us one of them so we can show you how to deal with them, you then repeat the technique on all of the other columns you need to apply it to.

    .

    Blank rows and columns in "input" data is a problem for Excel.  It doesn't handle them gracefully. 

    Blank rows/columns are typically included to make reading reports/output easier.

    One way to recreate the visual distinction between groups of data  is to use conditional formatting

    This link describes more about how to setup example data and how to share it with us

    Trouble Shooting - Share OneDrive Filehttps://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

    .

    .

    The article includes links to macros to randomize text in Word and numbers in Excel to preserve privacy

    .

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-05-16T19:58:38+00:00

    That is odd.

    You have mentioned a few things that give me pause:

    1. Excel is not recognizing the dates as dates.
    2. You show an example where 9/19 gets converted but 10/2 does not
      1. You attribute that to the blank row, but I think there is a different reason.

    In Excel, real dates are stored as numbers.

    On the Excel sheet with the dates, if you add a column with:

        =ISNUMBER(date_Cell), do ALL the dates return FALSE?  Or do some return TRUE?

    Thanks for your answer. I'm going to try this.

    Unfortunately, I cannot send out the workbook. I'm going to reproduce a small segment of it and forward it.

    Hi

    I've uploaded a sample of the workbook (I've replaced info on the left by cats and dogs)

    https://www.dropbox.com/scl/fi/kk8xsnf85n323mvfpshn5/Book1.xlsx?dl=0&rlkey=ly539kkpfqwshmz4xogja4s78

    Hi,

    Let me know if you have access to the Excel Workbook.

    Excel Workbook

    Was this answer helpful?

    0 comments