# Google Sheets: Histogram of time data (axis numbering)

I've tried making a histogram of my data, which looks like so:

``````12:00:02 PM

3:00:00 PM

12:30:00 PM

12:30:00 PM

2:00:00 PM

3:00:00 AM

…

``````

I highlighted the data and selected insert > chart > histogram.

And got:

The axes labels are completely screwed up:

1. It's formatted as numbers instead of dates!
2. Some entries are greater than 1!

I've tried everything I can think of. How do I fix this?

• I also tried asking on Quora, to no avail. Jan 12, 2020 at 2:52
• Could you please draw what the ideal result would be? For the data example you gave. I could not quite get what kind of histogram you want. Thanks!
– nic
Sep 25, 2020 at 2:55
• Same picture, but: The y axis should be numbered 1,2,3,4 ... with the units of "count". The x axis should have 1:00 am, 2:00 am ... Sep 25, 2020 at 5:31
• I have the same issue. X axis shows days as a decimal number, I think Y axis shows a count formatted as a time?
– yoyo
Sep 4, 2021 at 23:05

Unfortunately there's no nice way to fix this. There are two options I see:

#### 1. Convert time values into numbers (hours or minutes)

You can use `TO_PURE_NUMBER(x)` to convert a duration to a number, where the value `1` equals 24 hours. So to get hours you can use `24*TO_PURE_NUMBER(x)` or for minutes use `60*24*TO_PURE_NUMBER(x)`, etc.

Then when you create the histogram the y-axis will just be count and the x-axis will have those hours or minutes with a decimal part.

#### 2. Build your own histogram using a bar chart

On a separate sheet build a table of bucket boundaries, then use `COUNTIFS` to count the number of values in each bucket. This can look something like:

``````=ARRAYFORMULA(COUNTIFS(Sheet1!E\$2:E,">="&\$A2:\$A14,Sheet1!E\$2:E,"<"&\$A3:\$A15))
``````

(Edit: or use `FREQUENCY` -- see Travis's answer)

Where `A2:A15` are the bucket boundaries. Then, format the bucket boundaries however you like and put the table into a bar chart.

• Nice workaround, but yuck, come on google!
– yoyo
Sep 4, 2021 at 23:06

I'll just add that you can use the FREQUENCY function instead of COUNTIFS to populate your "buckets." If your buckets are in A2:A14 and your data is on Sheet1!E2:E, try:

``````=arrayformula(frequency(Sheet1!E2:E,A2:A14))
``````

Then, instead of using the histogram chart, just make a simple column chart from this table.