time interval frequency

[mrnrao11]

Hello
This is my first request for help. I have a sample data (day Collected Time of the day collected ), I would like to create a bin for each hour from time of day collected. For some reason I was not able to do using Solver. Can you please suggest me whats the wrong in my data.
Thanks,
Stan

[CK76]

You have data type mixed in your [Time of the day collected].

First clean it up by using =Value(F3) in G3 and copy down. Then copy the values over F column (as values only).

Then in M4:
=COUNTIFS($F$3:$F$133,">="&L4,$F$3:$F$133,"<"&MIN(1,L5))

[mrnrao11]

Many thanks, its very helpful, for some reason i was not able to change the time. Can you please let me know about =Value F3 means.

[mrnrao11]

00:12
00:19
00:19
01:09
01:17
01:22
01:36
01:43
01:43
02:21
02:37
03:11
03:52
03:52
03:52
04:35
04:35
04:52
05:42
05:42

I am doing something not correct. I am highlighting all the cells, format cells, time, selecting 1:30:55 PM. After this selection there was no change in the time like AM and PM. Can you please let me know your thoughts or what I am missing.
many thanks and wonderful weekend.
Thanks,
Stan

[protonLeah]

1. Custom format column 1: hh:mm:ss
2. Put 1 in G1, copy
3. Select times in F, Paste Special, Multiply

[FlameRetired]

Many thanks, its very helpful, for some reason i was not able to change the time. Can you please let me know about =Value F3 means.

If you are not aware of it dates and times are numbers. Dates are the count of days since Jan 1, 1900 which is day 1. The formatting is cosmetic. Times are decimal fractions of a day. 8/24=1/3 = 8:00 AM 12/24 = 1/2 = 12:00 PM 16/24 = 4:00 PM. The formatting is also cosmetic and does nothing to the values or data types.

It is easy to mistake text "dates" and "times" as actual dates and times. They are not and must be converted to numeric values.

VALUE is a spreadsheet function that does that. Type =VALUE(f3) and that will convert those text "numbers" into actual numbers that Excel can recognize as times. Then format the results to suit.

[mrnrao11]

Thank you so much

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.