Ask Your Question
0

Formula to separate data

asked 2017-10-16 08:58:18 +0100

davisonj gravatar image

Hi there

I have a list of times all in random order for each day of the month when calls were taken. Is there anyway I can write a formula to say: certain amount of calls were taken between 07:00 - 07:59, certain amount of calls were taken between 08:00 - 08:59, so on and so on....?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-10-16 10:06:33 +0100

JohnSUN gravatar image

Yes, it's very easy and simple calculation.

=SUMPRODUCT(N(HOUR(A:A)=11))

will counting calls from 11:00:00 to 11:59:59,99

edit flag offensive delete link more

Comments

Hey sorry I'm not so savvy with these sorts of things. Just to confirm I do =SUMPRODUCT(N(HOUR(A:A)=11)).

What do I put where the 'N' is?

'A:A' Are these my cells?

=11... What is this for?

Sorry for being so ignorant! I'm on a learning curve. Thank you

davisonj gravatar imagedavisonj ( 2017-10-16 10:25:33 +0100 )edit

Yes, most of it you understood quite correctly: A:A is really a column with time, 11 - it is start of calculated interval (11:00 AM), just change it to 8,9,10 etc for each hour in day. About N() - it is Calc's function to convert boolean value (result of compare - TRUE or FALSE) to integer (1 or 0)

JohnSUN gravatar imageJohnSUN ( 2017-10-16 10:36:11 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-16 08:58:18 +0100

Seen: 24 times

Last updated: Oct 16 '17