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!

  • I just did. I can't share the file directly because there is sensitive donor info, but i hope that this screencap helps – Cassandra Dec 27 '18 at 19:50
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),), " ")),)

  • Thank you so much for this info! trying it now, and will update asap. – Cassandra Dec 27 '18 at 21:26
  • Thank you!! It worked to format the dates exactly as i had hoped! Although it only formatted up to 3999, and left the last 7 cells blank. Any ideas for how to adjust for future additions to the column?..........................................................................................................................................Re: timestamps... any suggestions for how to capture that data separately? – Cassandra Dec 27 '18 at 21:38
0

You could also achieve it like this:

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

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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