how to format the date from trello report?

Question

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?

Answer 1

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))))