Help please

[Athlumneyabbey]

I am really new to excel and am trying to teach myself how to do things.

I am trying to copy a formula where the criteria changes for each subsequent cell ( I will also add I may not speak the right excel language - which I appreciate does not help matters

My formula will remain the same for all of the following roes but the time will change by 15 minutes each time

I have to do this for a full 24/7 Roster and am doing it cell be cell....... but there has to be an easier way?

To help (I hope) the formula is =COUNTIF(D2:D29,"<=9:30")-COUNTIF(E2:E29,"<=9:30") however for each row the time (in the example 09:30) will increase by 15 minutes

If anyone could help, you would save my little fingers and mem from losing my mind

Many thanks

EDIT: I am also hoping whoever reads this is fluent in typos!! Apologies

 

[etaf]

not sure i fully follow

I have to do this for a full 24/7 Roster and am doing it cell be cell.

is this 7 days a week, so the date changes
also 15mins for 24hrs would be 96 rows
you are only showing D2:D29 - so a lot less rows

you could set up a helper column to change the time by 15mins each row
so row 2 has 9:30 in and then row 3 - =I2+TIMEVALUE("0:15:0")
will add 15mins and that copied down and then the fomrula is

=COUNTIF($D$2:$D$29,"<="&I2)-COUNTIF($E$2:$E$29,"<="&I2)

copy down - see blow
but as i say I have completely miss understood that

Book2
	A	B	C	D	E	F	G	H	I
1
2						0			9:30
3						0			9:45
4						0			10:00
5						0			10:15
6						0			10:30
7						0			10:45
8						0			11:00
9						0			11:15
10						0			11:30
11						0			11:45
12						0			12:00
13						0			12:15
14						0			12:30
15						0			12:45
16						0			13:00
17						0			13:15
18						0			13:30
19						0			13:45
20						0			14:00
21						0			14:15
22						0			14:30
23						0			14:45
24						0			15:00
Sheet1

Cell Formulas
Range	Formula
F2:F24	F2	=COUNTIF($D$2:$D$29,"<="&I2)-COUNTIF($E$2:$E$29,"<="&I2)
I3:I24	I3	=I2+TIMEVALUE("0:15:0")

 

[Athlumneyabbey]

not sure i fully follow

is this 7 days a week, so the date changes
also 15mins for 24hrs would be 96 rows
you are only showing D2:D29 - so a lot less rows

you could set up a helper column to change the time by 15mins each row
so row 2 has 9:30 in and then row 3 - =I2+TIMEVALUE("0:15:0")
will add 15mins and that copied down and then the fomrula is

=COUNTIF($D$2:$D$29,"<="&I2)-COUNTIF($E$2:$E$29,"<="&I2)

copy down - see blow
but as i say I have completely miss understood that

Book2
	A	B	C	D	E	F	G	H	I
1
2						0			9:30
3						0			9:45
4						0			10:00
5						0			10:15
6						0			10:30
7						0			10:45
8						0			11:00
9						0			11:15
10						0			11:30
11						0			11:45
12						0			12:00
13						0			12:15
14						0			12:30
15						0			12:45
16						0			13:00
17						0			13:15
18						0			13:30
19						0			13:45
20						0			14:00
21						0			14:15
22						0			14:30
23						0			14:45
24						0			15:00
Sheet1

Cell Formulas
Range	Formula
F2:F24	F2	=COUNTIF($D$2:$D$29,"<="&I2)-COUNTIF($E$2:$E$29,"<="&I2)
I3:I24	I3	=I2+TIMEVALUE("0:15:0")

Very many thanks for your reply which was very helpful

 

[etaf]

you are welcome