Frequency Function with Times

[Bench]

Hi,

Struggling to get the Frequency Function to work with time.

In Column B i have dd/mm/yyyy hh:mm this is exported from a Database.

I want to know how many cases arrived between each hour of the day.

I've used Text=(B2,"hh:mm") and also tried Text=(B2,"hh:mm:ss") to extract the time in.

I've then added 00:00 to 23:00 in O2:O25 and used the array formula:

=FREQUENCY(C2:C60,O2:O26)

But i only get zeros back.

Where am i going wrong?

 

[pgc01]

Hi Bench

Changing the format of the values achieves nothing since the excel function uses the values.

The Text function returns (as its name says) a text. The Frequency function works with numbers and so you won't get any result.

Just use O2:O25 as the bins range, and for the values use directly the range in column A (if they are really datetimes).

To check if a value in column A are numeric, use =ISNUMBER(A2). If the result is False, then convert the values in column A to excel datetime before applying the Frequency function.

 

[Bench]

Hi Pgc01,

Thanks for your reply, it does indeed work if i add "dd/mm/yyyy" to my bin, the issue is that i would like to trend the times of day things come in, hence why i was trying to lose the date and simply keep the hh:mm:ss, the only way i know how to do this is using the TEXT formula, is there another way to seperate the cells contents? both mid and right give funny results.

Thanks

 

[pgc01]

Bench

The unit for the datetime in excel is the day. This means that the integer part of the value is the date and the decimal part of the value is the time.

To get just the time out of a datetime value you just have to extract the decimal part

=MOD(A2,1)

and format the cell as time.

 

[Bench]

thanks

 

[syedbokhari]

If I want to do this with dates!

How can I ?

I just want the frequencies of people between certain years 1930,1940,1950,1960 etc!

Excel Workbook
	N
2	4-Dec-1950
3	4-Oct-1949
4	4-Oct-1949
5	11-Sep-1946
6	11-Sep-1946
7	17-Jul-1949
8	17-Jul-1949
9	19-Jul-1941
10	19-Jul-1941
11	4-Jun-1931
12	4-Jun-1931
13	5-Oct-1940
14	9-Feb-1964
15	9-Feb-1964
16	9-Feb-1964
17	9-Feb-1964
18	9-Feb-1964
19	4-Mar-1968
20	4-Mar-1968
21	4-Mar-1968
22	4-Mar-1968
23	18-Sep-1956
24	8-Jun-1959
25	8-Jun-1959
26	26-Jun-1937
27	26-Jun-1937
28	26-Jun-1937
29	26-Jun-1937
30	26-Jun-1937
31	26-Jun-1937
Data Cleaned-Blanks Removed

 

[syedbokhari]

Hey Man!

Sorry to be a pain!

However I keep on having no success sadly.