Count a certain # of interaction on a timeline.

[xatomicx]

Hi everyone, i'm building a chart, in this chart i need to calculate a certain number of interaction based on time.

Example...
i have sheet ranging from sheet1 to sheet4,
On each sheet, we set a drop down menu with a timestamp of the interaction with format " mm/dd \/ hh:mm ".

i want to know, the number of timestamps between certains hours at certain date.

How do i do a formula that will count on all my sheet, all timestamp between 8:00 AM to 8:30 AM on april 4th?

i'm having issue figuring out how to set a "in between" countif, respecting date and hours.

If anyone can help me build a formula for my example of 8-8:30, i will be able to adapt it to all other hours of the day.

Thanks!

[Leith Ross]

Hello xatomicx,


The easiest approach is to use one formula for each sheet and then sum the results. In this example cells "A1:A10" contain the time stamps. The date and time are hard coded here for visual clarity. You can replace these values with the contents from two cells that represent the start date/ time and end date/time.

=SUMPRODUCT(--(Sheet1!A1:A10>=DATEVALUE("4/4/2012 08:00")),--(Sheet1!A1:A10<=DATEVALUE("4/4/2012 08:30")))

Change Sheet1! in the formula above to the name of the next sheet. If your sheet name has spaces in it, the name will need to be enclosed with single quotes. For example, 'Quarterly Report'!A1:A10.

[xatomicx]

Thank you,

Can i set the date value to disregard a part of my timestamp?

Example;

My timestamps will look like:
04/02 / 00:11
04/02 / 08:48
04/02 / 08:52
04/02 / 08:53
04/02 / 08:56
04/02 / 09:37
04/02 / 09:44
04/02 / 09:52
04/03 / 15:35
04/03 / 18:05
04/03 / 19:18
04/03 / 21:52

But in my datevalue, i wish to search for everything between 8-9, no matter what is the date, as long as it is between 8 to 9.

You suggest i use the content from 2 cells, but if i put cell1 as 8:00 AM and cell2 as 9:00 AM, it reject my formula with a #value.

My formula looks like it:
=SUMPRODUCT(--(hdcore1!F$1:F$2500>=DATEVALUE(A2)),--(hdcore1!F$1:F$2500<=DATEVALUE(A3)))

Datevalue A2 = 8:00 AM
Datevalue A3 = 9:00 AM

I know something is wrong in the way i am setting the formula, as i think the date of the timestamp might conflict, as i only want to use the hours of the timestamp.

Any clues?

[Leith Ross]

Hello xatomic,

If you want to use only the time and not the date then change the DATEVALUE to TIMEVALUE.

[xatomicx]

True, i could use timevalue

Altough, when i'm building my formula using timevalue, its still give me a #value error :/

[Leith Ross]

Hello xatomicx,

The dates and times must in string format like "4/10/2012" or "08:00" in the cell.

[xatomicx]

Please Login or Register  to view this content.

Formula doesnt kick back with any error this time, but gives me a 0 as a results, when should be around 100's.

The timestamp are done with a drop down that link to list: =now(), so it auto stamps dates/times.

Could my formula go in circle and read =now() instead of the time/hour that i actually see in the cells?

[xatomicx]

no matter what i do the formula results bring me to 0.

Sheet is called hdcore1, and data is between f1 and f2500, and data shows in those cells as:

04/02 / 00:11
04/02 / 08:48
04/02 / 08:52
04/02 / 08:53
04/02 / 08:56
04/02 / 09:37
04/02 / 09:44
04/02 / 09:52
04/03 / 15:35
04/03 / 18:05
04/03 / 19:18
04/03 / 21:52

I really don't know what's wrong, all elements are in for it to work.

Anyone see what i don't see? maybe its the => <= ?

[xatomicx]

any clue?

[Leith Ross]

Hello xatomicx,

My best guess is your system is not recognizing the time stamp as a valid date and time. The formula below will remedy that. You will need to make some changes to the formula like the range of time stamp range, start and stop times which can be cells with time strings. You will need to confirm this formula with CTRL+SHIFT+ENTER as it is an array formula.

=SUMPRODUCT(--(IF(ISERR(TIMEVALUE(MID(F1:F100,9,5))>=TIMEVALUE("08:00")),FALSE,TIMEVALUE(MID(F1:F100,9,5))>=TIMEVALUE("08:00"))),--(IF(ISERR(TIMEVALUE(MID(F1:F100,9,5))<=TIMEVALUE("10:00")),FALSE,TIMEVALUE(MID(F1:F100,9,5))<=TIMEVALUE("10:00"))))

[xatomicx]

Thank you,

might sound like a noob question, but where in the formula should i set the "sheet1" to look on first sheet?

[xatomicx]

My first time with "array formula", half of the formula looks like chineese for me :P i can decypher half of what it mean

[Leith Ross]

Hello xatomicx ,

You add the sheet name before the range like this, Sheet1!F1:F100. If your sheet name has spaces in it then you need to enclose the sheet name in single quotes like this, 'Quarterly Report'!F1:F100

[xatomicx]

Thanks, i really appreciate your assistance

It is unfortunately, still a no-go, 0 as results.

right formula should look like:

Please Login or Register  to view this content.

cells to search in looks like:

04/11 / 08:26
04/11 / 08:26
04/11 / 08:40
04/11 / 08:43
04/11 / 08:53
04/11 / 08:53
04/11 / 08:56
04/11 / 09:09

But i've spot something... if i click on a cell, for example 04/11 / 08:26, on top of the screen it shows under Fx: 4/11/2012 8:26:11 AM

Its just that the cell was reformatted. Can it change something?

[Leith Ross]

Hello xatomicx,

I copied the examples you posted into a workbook in column "F". Not additional formatting was done on these cells and the formula worked perfectly. If you can post a copy of the workbook, you should. Otherwise, we may never find the problem.

[xatomicx]

Alright, i'll figure out how to post a workbook, and we'll start from there

Thanks!

[xatomicx]

here's the file, i removed the sensible data, and the formula is located on the "call flow chart" sheet, beside 8am.

i will, once the formula is running, adapt it for the different hours and multiple sheets, but first, i just want the code to be functionning :/

[xatomicx]

solution: wich is also the complete formula

Please Login or Register  to view this content.