0

In the below sheet Column A represents Date. But some dates not recognized by google sheets. For example from cell A5 to A51 not recognized. I copied Column A to Column G and changed the date format of Column G to DD-MM-YYYY. Also, I changed the time zone from US to India. But still problem not solved. I tried "Date" formula in H2. It also failed. I need the date in the exact format DD-MM-YYYY in Column J. Please find a solution. PFA

https://docs.google.com/spreadsheets/d/1Kej4tP6pofeOSU9flhICVR7owIYwNHnTaoNbLuO8WBs/edit?usp=sharing

1 Answer 1

2

You are using a date format where 02-01-2022 means 2 January 2022 rather than 1 February 2022. When you directly enter that value in a spreadsheet whose locale assumes the m/d/yyyy date format, the value gets interpreted as 02/01/2022, i.e., 1 February 2022, the opposite of what you want.

There are only 12 months in a year, and most of your values start with a number that is greater than that. As a result, most of the values in column A are not numeric dates but text strings that just look like dates. The rest of the values have been interpreted as dates but have their day and month reversed.

The silver lining is that the data appears to have been entered in the same invalid format throughout. This means that the data can be fixed, like this:

=arrayformula( 
  if( 
    len(A2:A), 
    to_date( value( 
      regexreplace(to_text(A2:A), "(\d+).(\d+).(\d+)", "$3-$2-$1") 
    ) ), 
    iferror(ø) 
  ) 
)

See the new Solution sheet in your sample spreadsheet. The formula is in cell G1. You can overwrite your original data with these corrected values using Edit > Paste special > Paste values only.

To best avoid confusion whether 12/1 means 1st of December (month/day) or 12 January (day/month), make sure that File > Settings > Locale matches your real locale. The default locale is set based on your Google Account details.

One very good strategy is to use the ISO8601 standard date format yyyy-MM-dd consistently everywhere. It works in any locale.

See this answer for an explanation of how date and time values work in spreadsheets.

The formula uses if() and will thus return dateserial values that need to be formatted as dates to be readable through Format > Number > Date or a custom date format of your choosing. You can avoid that by using a closed-end range such as A2:A120 and removing the if(), like this:

=arrayformula( 
  to_date( value( 
    regexreplace(to_text(A2:A120), "(\d+).(\d+).(\d+)", "$3-$2-$1") 
  ) ) 
)

That will give dates in the default date format of the spreadsheet. To set another date format directly in the formula, use this pattern:

=let( 
  format_, lambda(v,f,map({v},lambda(v,query(v,"format Col1'"&f&"'", 0)))), 

  fixed, arrayformula( 
    if( 
      len(A2:A), 
      to_date( value( 
        regexreplace(to_text(A2:A), "(\d+).(\d+).(\d+)", "$3-$2-$1") 
      ) ), 
      iferror(ø) 
    ) 
  ), 
  
  format_(fixed, "dd-MM-yyyy") 
)
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.