0

the format is this 1984-03-03T00:00:00.000Z but require month, date, and year because i want to create a pivot report in google sheets for a monthly report.

can anyone help me with this?

1 Answer 1

0

ISO-8601 Date Format

Your values are in ISO-8601 format. The simplest way to convert them is to use the DATEVALUE function.

DATEVALUE Function

DATEVALUE converts a provided date string in a known format to a date value.
Syntax: DATEVALUE(date_string)

Basic Formula
=DATEVALUE(LEFT(A1,10))

# Time would be similar
=TIMEVALUE(MID(A1,12,11)) 



You would paste the formula down the column or alternatively use the BYROW function.

BYROW Function

BYROW groups an array by rows by application of a LAMBDA function to each row.
Syntax: BYROW(array_or_range,LAMBDA)

BYROW Formula
=BYROW(A:A, LAMBDA(r, 
   IF(r<>"", 
     DATEVALUE(LEFT(r,10)),
     IFERROR(1/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.