3

screenshot of data in google sheets

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!

0

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

0

You could also achieve it like this:

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

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.