CODEWITHSUNDEEP
google-sheets
Saksham
Saksham

Reputation: 13

Date Conversion in Google Sheets

I am struggling to convert a bunch of dates in Google Sheets.

My dates are in the format mentioned in this image.

Some dates have AM/PM in them, while some have a / instead of a -.

I need to convert them to yyyy"-"mm"-"dd" "hh":"mm":"ss while converting the time to 24hour format in case of PM.

Does anyone know a way to achieve this?

Upvotes: 0

Views: 364

Answers (2)

doubleunary
doubleunary

Reputation: 19238

The problem is that most of your data uses the American date convention m/d/yyyy while your spreadsheet uses the Indian d/m/yyyy date convention. But then again, some of the data appears to already be in the ISO8601 format you are requesting as the result format. To convert all the dates, use this:

=arrayformula( 
  if( 
    isnumber(A2:A) + isblank(A2:A), 
    A2:A, 
    regexreplace(A2:A, "^(\d+)/(\d+)/(\d+)(.+)", "$3-$1-$2$4") + 0 
  ) 
)

Format the result cells as Format > Number > Date time or as the custom format you mentioned. See the new Solution column in your sample spreadsheet.

Upvotes: 1

Erik Tyler
Erik Tyler

Reputation: 9355

Suppose your raw data is in A2:A. Clear B2:B and place the following into B2:

=ArrayFormula(IF(A2:A="",,IFERROR(DATEVALUE(A2:A&"")+TIMEVALUE(A2:A&""),DATEVALUE(A2:A)+TIMEVALUE(A2:A))))

This formula has error control built in, to handle the raw data if there is a mix of strings and real date-times.

Next, select the entire Column B. Apply Format > Number > More Formats > Custom number format and enter the following in the text field at the top: yyyy-mm-dd hh:mm:ss

Upvotes: 1

Related Questions