Google-sheets – Conditional formatting for columns, date & time specific

conditional formattingformulasgoogle sheetsgoogle-sheets-dates

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!

Best Answer

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

Related Topic