Delimit by space?
Or =right 12 characters?
I have some data with events that happened in a format like this:
07-Jan-2021 08:13:57 GMT
It has come from .csv into Excel and as far as I can tell is not formatted as Date/Time but is just text, hence the 'GMT'.
What I really want to do if count these events into hourly buckets - so this one happened between 08:00 and 08:59 and disregard the date.
I'm sure this is possible and I could probably have done it myself when I used Excel a lot but, for a Friday afternoon, it has me stumped.
Can anyone assist?
Or should I just knock off early and go to the pub?
Have another column (say X) = INT(MID("date", 13,2)) to give the hour as an integer. And then use COUNTIF to group these into hourly buckets (e.g. COUNTIF("x:x",8) for the number in the 8:00-8:59 time slot.
How often do you need to do this?
If a one off then add a new column to the start of the sheet use that a index in case you ever need to view the data in its original sequence. Then use text to columns split the date, hour, minuet fields out and sort by hour.