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:
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'")