excel date formatting issue

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel date formatting issue

Excel date formatting issue

resolvedResolved · High Priority · Version 365

Shahrier has attended:
Excel Introduction course

Excel date formatting issue

Data attached:
- Raw data – Column A: the respondent ID
- Raw data – Column B: their start time
- Raw data – Column C: their end time
- Calculation – Column D: their completion time;
o This column subtracts the respondents’ start time from their end time, and multiplies it by 1440 – the number of minutes in a day.
o This ultimately gives us the number of minutes each respondents took, to complete the survey

Issue:
The issue we are facing is attributable to the difference in date formatting between the US (month / day / year) and the UK (day / month / year). This issue only seems to be affecting us in the UK. If they in US update the data in the same way, they do not receive the error.
As you can see in the document, an error appears for rows 33 to 65, that is created by that formatting difference.
By example, for row 33, our Excel considers the date is the 11th of month 13, instead of adjusting to the 13th of November.

We were wondering whether you would have a solution to fix this issue, considering that we will be receiving more data, and that a manual exercise (splitting the day / month / year, and concatenating together) is not really feasible.

RE: Excel date formatting issue

Hi Shahrier,

Thank you for your question to the forum.

If you need to apply date formatting so all US dates convert to UK dates then the most efficient method would be to use Power Query within Excel. This will not only set up the switch but will also apply to new data coming in where the conversion steps will be automated.

Here's how to do it:

1. In Excel select your datasheet and go to DATA > FROM TABLE/RANGE to open Power Query

2. Right click the date heading (US date) and go to CHANGE TYPE > USING LOCALE

3. In the 'Data Type' field, set to 'Date'

4. In the 'Locale' field, set to 'English (United States)' and click OK

5. Go to CLOSE & LOAD > CLOSE & LOAD to open up a new sheet with the correct UK formatting

6. Now add more data to the original datasheet including US dates and then select a cell in the new data and go to QUERY > REFRESH - this will automatically update the source data and any calculations linked to it

I hope this has given you a solution to your problem. Please let me know if it has worked.

Kind regards
Martin Sutherland
(IT trainer)



Mon 18 Dec 2023: Automatically marked as resolved.


 

Excel tip:

Conditional Formatting in Excel 2010

If you have lots of data in a spreadsheet, you may find that it is easier to read if you highlight some of the values. This is Conditional Formatting and here's how to use it:

1) Select the data you wish to apply the format to and click Conditional Formatting
2) A list of options will then appear, from this list, choose the format you wish to display e.g. find all cells with a value less than 0
3) Excel will then highlight all of these cells

To remove this: select the highlighted cells, click the drop down on the Conditional Formatting icon and select Clear Rules from selected cells.

View all Excel hints and tips


Server loaded in 0.08 secs.