Want to convert "2021/06/29 1:28:45 AM GMT+5:30" to this format "6/29/2021 1:28:45" removing the timezone and AM/PM tag in google sheets using a formula if possible.


Use regexextract() and coerce the string to a numeric value, like this:

=regexextract( A2, "(?i)^(.+ [ampm]+)" ) + 0

Format the result cell as Format > Number > Date time.

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


You'll need a way to convert PM times to the 24-hour system. Supposing your raw string were in A2, try this in B2:

=REGEXREPLACE(A2," [AP].+$","")*1+IF(REGEXMATCH(A2,"P"),0.5,0)

If you need to process an entire range of such strings:

=ArrayFormula(IF(A2:A="",,REGEXREPLACE(A2:A," [AP].+$","")*1+IF(REGEXMATCH(A2:A,"P"),0.5,0)))

These formulas will likely return values in the 40000 range with a decimal portion. This is the raw date/time format Google Sheets uses (representing number of elapsed days since an origin data of December 30, 1899, with the decimal portion representing partial days, hence time of day). So you will want to select the output cell or range and choose Format > Number > Date time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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