Format Array date and time on Google Sheet-CodePudding

I'm would like to use Array Formula to use the data from another sheet and obtain only the Date from the Date and Time Data.

Example, In Sheet 1, there will be a list of date and time date.

In Sheet 2, I would like to use a formula to return the date without the time:

CodePudding user response：

Try this formula-

=INDEX(DATEVALUE(Sheet1!A1:INDEX(Sheet1!A1:A,COUNTA(Sheet1!A1:A))))

Or QUERY() function.

=QUERY(Sheet1!A:A,"select A where A is not null format A 'dd-mmm-yyyy'")

Edit: Use below formulas-

=INDEX(SPLIT(SUBSTITUTE(FILTER(Sheet1!A2:A,Sheet1!A2:A<>""),",","",1),","),,1)

Then use cell format as desired date formats. Or below QUERY() function to format it.

=QUERY(INDEX(SPLIT(SUBSTITUTE(FILTER(Sheet1!A2:A,Sheet1!A2:A<>""),",","",1),","),,1),"format Col1 'dd mmm yyyy'")

CodePudding user response：

Method 1:

Convert into string (without the need to specify the format)

=ArrayFormula(TEXT('Sheet 1'!A2:A,"dd MMM yyyy"))

Method 2:

Extact the date value and set the format

=ArrayFormula(DATEVALUE('Sheet 1'!A2:A))

Format: dd MMM yyyy

Reference: