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 Intro/intermediate 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

This is a duplicate post.
See Original post

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Select blank cells automatically

Get Excel to find any blank (empty) cells in a region for you by:

1. Selecting the appropriate region from your spreadsheet.

2. On the menu bar, go to Edit - Go to.

3. Click the 'Special' button, then select Blanks and click OK.

View all Excel hints and tips


Server loaded in 0.08 secs.