Conditional formatting for columns, date & time specific?

Question

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this:

2018-11-01T20:16:52

I have tried multiple approaches to sorting and formatting these but to no avail.

Ideally I would like to have date and time separate, but if need be I could drop the time and have the date only.

I am using column B, rows 2-4006.

I think if I can successfully set a conditional format for the entire column of =10 characters (or perhaps <11 ??) I could then possibly sort the format data to a date time format that I prefer (so from 2018-11-01 to 11/01/18).

If this makes sense to anyone and you can offer some suggestions, please feel free to message me!

Answer 1

the issue is that T in 2018-11-01T20:16:52

and the solution is to insert a new column after B column and paste this into C2 cell:

=ARRAYFORMULA(IF(B2:B4000<>"",
             MID(B2:B4000,6,2)&"/"&
             MID(B2:B4000,9,2)&"/"&
             MID(B2:B4000,3,2),))

for time separation use:

=IFERROR(ARRAYFORMULA(SPLIT(IF(B2:B4000<>"",
                           MID(B2:B4000,6,2)&"/"&
                           MID(B2:B4000,9,2)&"/"&
                           MID(B2:B4000,3,2)&" "&
                         RIGHT(B2:B4000,8),), " ")),)

Answer 2

You could also achieve it like this:

=ARRAYFORMULA(SPLIT(B2:B4000, "T"))

The date and time are shown automatically in the adjoining columns.

Answer 3

Formula

=ArrayFormula(ARRAY_CONSTRAIN(SPLIT(B2:B,"T"),COUNTA(B2:B),1))

To separate date-time values formatted as 2018-11-01T20:16:52 (text values)

• use SPLIT
  • use an open-ended reference (B2:B)to split values added lately without having to edit the formula
  • use T as separator.
• use ARRAY_CONSTRAIN to return split the values of non-empty cells.
  • Use COUNTA to calculate the how many cells on B2:B have text values.
• use ARRAYFORMULA to return an array of values instead of a single value.