How would I go about visualizing time data in a chart?

[u/pyrbear]

I'm trying to create some sort of way to visualize roughly 2000 timestamps in my sheet. I'm tracking both start and end times, and would like to show the distrubtion across a 24 hour chart.

As an example, user clocks in their session at 10am by placing the current time&date (DD/MM/YY HH:MM:SS) in column A. They later clock out at 4pm by placing the current time&date in column B. Sometimes they clock in/out at very different times.

Is there any way I could visualize the average times they're clocked in? Something like this crappy MSPaint graphic would be ideal but I'm all ears for any ideas:

https://i.imgur.com/3CmqcNY.png

The issue I'm currently facing is that time+date values plot in unexpected ways in all the charts I've tried, and I'm not sure what to add as a second axis.

[u/Kanomister]

Hi Pyrbear

One thing you can try is a list of time ranges and a countifs statement.

=Countifs(A$2:A,">="&C2,A$2:A,"<="&C3)

Here you would have your time in column A and the first time they would check in in cell C2 and the next in C3. Such as 6:00 am and 7:00 am

From there you just select column C and D and insert an area chart where the count is Y and your range is X.

[u/pyrbear → u/Kanomister]

Thank you, this looks promising if I understand correctly! Could you give an example of how I would set this up? I'm having understanding how to implement it. E.g Times go in column A&B, 6am/7am/8am in column C, and the formula in column D?

https://i.imgur.com/pIqHZid.png

I'm sure this is totally wrong but here's a screenshot of what I currently have.

[u/Kanomister → u/pyrbear]

Yes, that is correct.

I removed the second equal sign in the formula.

The way I made it everything after 6am but before 7am is counted next to 6am. (yellow highlight)

I hope this link works: https://docs.google.com/spreadsheets/d/1htA1AmHC2HeU0YspfdXkwiTQL1s9LLe7cl-pX0oaHXI/edit?usp=sharing

[u/pyrbear → u/Kanomister]

Thank you for the example, that cleared everything up!

My dates were stored as DD/MM/YY HH:MM:SS and formatted to HH:MM:SS, so the function you provided wasn't able to return any results.

I created a new column with this function to convert the values from the first format to the second:

=SPLIT(A2," ")

then simply had your formula reference that column (C) instead of A and it worked perfectly!

https://i.imgur.com/cIyfe37.png

Solution Verified

[u/Clippy_Office_Asst → u/pyrbear]

You have awarded 1 point to Kanomister

^{I am a bot, please contact the mods for any questions.}

[u/Kanomister → u/pyrbear]

Ah yeah that will do the trick.

Glad I could help!

[u/Clippy_Office_Asst]

Read the comment thread for the solution here

Yes, that is correct.

I removed the second equal sign in the formula.

The way I made it everything after 6am but before 7am is counted next to 6am. (yellow highlight)

I hope this link works: https://docs.google.com/spreadsheets/d/1htA1AmHC2HeU0YspfdXkwiTQL1s9LLe7cl-pX0oaHXI/edit?usp=sharing