0

The date on my excel sheet is 3/1/2019 and excel interprets it as March 1 but I want it to interpret it as January 3. I have tried formatting the cells but it just changes the position of data. Please help

Hennes
  • 65,804
  • 7
  • 115
  • 169
user1005883
  • 1

1 Answers1

0

Fixing Wrong Date Format

The value viewed in the cell is a formatted representation of the Date (&Time) serial number. You will have to change the serial number by transposing the month and day.
After fixing the current dates but before entering additional dates, be sure the cell format is correct and / or the locale is configured correctly for the date format desired.

To transpose these Bad dates:

  • Assuming the Bad dates are in column A.
  • Create a helper column B.
  • B=Date(year(A1),day(A1),month(A1)) .
    • Only apply this formula to the cells with the bad 'date' data.
    • Some of the cells in column A will likely be strings or the correct dates.
  • Depending on the number of correct dates, further steps can be taken to minimize the amount of manual checking needed.
    • Wrap the formula in a test to see if the result is a valid date, if it is not, leave it as the original value of A1.
    • If the dates are sequential, this can be used in a more complex formula, but is not worth the effort unless there are thousands Dates.

Column A1 may have the correct date if a date such as 13/1/2019 was entered. Since Excel was expecting MM/dd/yyyy, this value would not have been converted to a date serial number. These correct dates would be left as a string. If the date format of these cells was updated to dd/MM/yyyy, these correct dates would then be saved as a date serial number. Best case scenario is there are good "string dates", in among the bad dates. If the correct date format has already been applied, then these correct string dates became actual date serial numbers of the correct date (worse case scenario).

If the format has not been updated, apply a wrapper to test for string or error, and use the original value of A1 if String (or Error).

Once every value in column B is one hundred percent correct, copy column B. "Paste Special - Values" onto column A. Delete the helper column (column B).

Ted D.
  • 790