counting time

[orsm6]

I have a start time (BG13) and a finish time (BH13) which could contain any start and finish time (even start time today, finish time is tomorrow)..... is it possible to count how many 30 minute blocks are between (and including) those times?

as example 5:30am start, 7:38 start (it is 2 hours I know) but there are 5 x 30 minute values.....

5:30 = 1
6:00 = 2
6:30 = 3
7:00 = 4
7:30 = 5

hope this makes sense.

 

[sheetspread]

"Between those times" (5:30 = 0) and "5 x 30 minute values" is a contradiction, but you seem to be asking for:

Excel 2010
	A	B	C
1	5:30 AM	7:38 AM	5
Sheet13

Cell Formulas
Range	Formula
C1	=1+ROUND((B1-A1)*24*2,0)

roundup works too

 

[orsm6]

thank you

 

[orsm6]

OK, this solution hasn't done exactly what I need to.

in the examples above, I am trying to count the number of half hour intervals. this is the formula I am using: =FLOOR((BH13-BG13)*24,0.5)*2
rounding does not always work in every case, here is an example

start time: 8:55am - finish time 2:30pm...... If you count how many 30 minute intervals there are (including 2:30) you get 12.... start at 9am, then 9:30, then 10 etc etc
the formula I am using above returns 11 because it doesn't include the 2:30

the example at the top of this thread using a formula returns 4, but it isn't counting the 5:30am time and I need it to return 5.

hope this makes sense and is even possible to do.

cheers

 

[sheetspread]

The formula I wrote above returns 12, do you not want it to?

Excel 2010
	A	B	C
1	8:55 AM	2:30 PM	12
Sheet1

Cell Formulas
Range	Formula
C1	=1+ROUND((B1-A1)*24*2,0)

 

[orsm6]

it does work, but it also doesn't work haha.

here are some more examples.
- 3:00am to 6:45am = 7 instances your formula returns 9 (so I then removed the 1= part of formula, it returns 8)
- 4:15am to 11:00 pm = 14 instances, ROUND((BH42-BG42)*24*2,0) works for this time.
- 4:45am to 7:30am = 5 instances but the formula returns value of 6

 

[FormR]

here are some more examples.
- 3:00am to 6:45am = 7 instances your formula returns 9 (so I then removed the 1= part of formula, it returns 8)
- 4:15am to 11:00 pm = 14 instances, ROUND((BH42-BG42)*24*2,0) works for this time.
- 4:45am to 7:30am = 5 instances but the formula returns value of 6

Hi, shouldn't the expected result for the first example be 8? And the last example be 6?

If not, why not?

Excel 2013/2016
	A	B	C
1	05:30	07:38	5
2	03:00	06:45	8
3	04:15	11:00	14
4	04:45	07:30	6
5	05:30	05:30	1
6	05:30	06:00	2
7	05:30	06:30	3
8	05:30	07:00	4
9	05:30	07:30	5
10	08:55	14:30	12
Sheet1

Cell Formulas
Range	Formula
C1	=1+((FLOOR(B1,"00:30")-CEILING(A1,"00:30"))/"00:30")

 

[sheetspread]

This matched everything in FormR's sample too, not sure how it will look on your actual data:

Excel 2010
	A	B	C
1	5:30	7:38	5
2	3:00	6:45	8
3	4:15	11:00	14
4	4:45	7:30	6
5	5:30	5:30	1
6	5:30	6:00	2
7	5:30	6:30	3
8	5:30	7:00	4
9	5:30	7:30	5
10	8:55	14:30	12
Sheet2

Cell Formulas
Range	Formula
C1	=INT((B1-A1)*24*2)+1

 

[markmzz]

Hi!

If I understand correctly what you want, so the formula below (that is not the most smart, but I think do the job) can help you.

In B54 and copy the range B54:C54 to the right:

=SUMPRODUCT(--(RIGHT(ROW(INDIRECT(ROUND(IF(B1,B1*24,0.5),2)*100&":"&
ROUND(((B52 < B1)+B52)*24,2)*100)),2)={"00","50"}))+(B1=0)


[TABLE="class: grid, width: 979"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start Time[/TD]
[TD] 05:30[/TD]
[TD][/TD]
[TD] 08:55[/TD]
[TD][/TD]
[TD] 03:00[/TD]
[TD][/TD]
[TD] 16:45[/TD]
[TD][/TD]
[TD] 04:45[/TD]
[TD][/TD]
[TD] 00:00[/TD]
[TD][/TD]
[TD] 04:31[/TD]
[TD][/TD]
[TD] 23:00[/TD]
[TD][/TD]
[TD] 23:00[/TD]
[TD][/TD]
[TD] 23:31[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD] 05:30[/TD]
[TD] 01[/TD]
[TD] 09:00[/TD]
[TD] 01[/TD]
[TD] 03:00[/TD]
[TD] 01[/TD]
[TD] 17:00[/TD]
[TD] 01[/TD]
[TD] 05:00[/TD]
[TD] 01[/TD]
[TD] 00:00[/TD]
[TD] 01[/TD]
[TD] 05:00[/TD]
[TD] 01[/TD]
[TD] 23:00[/TD]
[TD] 01[/TD]
[TD] 23:00[/TD]
[TD] 01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD] 06:00[/TD]
[TD] 02[/TD]
[TD] 09:30[/TD]
[TD] 02 [/TD]
[TD] 03:30[/TD]
[TD] 02 [/TD]
[TD] 17:30[/TD]
[TD] 02 [/TD]
[TD] 05:30[/TD]
[TD] 02 [/TD]
[TD] 00:30[/TD]
[TD] 02 [/TD]
[TD] 05:30[/TD]
[TD] 02 [/TD]
[TD] 23:30[/TD]
[TD] 02 [/TD]
[TD] 23:30[/TD]
[TD] 02 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD] 06:30[/TD]
[TD] 03[/TD]
[TD] 10:00[/TD]
[TD] 03 [/TD]
[TD] 04:00[/TD]
[TD] 03 [/TD]
[TD] 18:00[/TD]
[TD] 03 [/TD]
[TD] 06:00[/TD]
[TD] 03 [/TD]
[TD] 01:00[/TD]
[TD] 03 [/TD]
[TD] 06:00[/TD]
[TD] 03 [/TD]
[TD] 00:00[/TD]
[TD] 03 [/TD]
[TD] 00:00[/TD]
[TD] 03 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD] 07:00[/TD]
[TD] 04[/TD]
[TD] 10:30[/TD]
[TD] 04 [/TD]
[TD] 04:30[/TD]
[TD] 04 [/TD]
[TD] 18:30[/TD]
[TD] 04 [/TD]
[TD] 06:30[/TD]
[TD] 04 [/TD]
[TD] 01:30[/TD]
[TD] 04 [/TD]
[TD] 06:30[/TD]
[TD] 04 [/TD]
[TD] 00:30[/TD]
[TD] 04 [/TD]
[TD] 00:30[/TD]
[TD] 04 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD] 07:30[/TD]
[TD] 05[/TD]
[TD] 11:00[/TD]
[TD] 05 [/TD]
[TD] 05:00[/TD]
[TD] 05 [/TD]
[TD] 19:00[/TD]
[TD] 05 [/TD]
[TD] 07:00[/TD]
[TD] 05 [/TD]
[TD] 02:00[/TD]
[TD] 05 [/TD]
[TD] 07:00[/TD]
[TD] 05 [/TD]
[TD] 01:00[/TD]
[TD] 05 [/TD]
[TD] 01:00[/TD]
[TD] 05 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 11:30[/TD]
[TD] 06 [/TD]
[TD] 05:30[/TD]
[TD] 06 [/TD]
[TD] 19:30[/TD]
[TD] 06 [/TD]
[TD] 07:30[/TD]
[TD] 06 [/TD]
[TD] 02:30[/TD]
[TD] 06 [/TD]
[TD] 07:30[/TD]
[TD] 06 [/TD]
[TD] 01:30[/TD]
[TD] 06 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 12:00[/TD]
[TD] 07 [/TD]
[TD] 06:00[/TD]
[TD] 07 [/TD]
[TD] 20:00[/TD]
[TD] 07 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 03:00[/TD]
[TD] 07 [/TD]
[TD] 08:00[/TD]
[TD] 07 [/TD]
[TD] 02:00[/TD]
[TD] 07 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 12:30[/TD]
[TD] 08 [/TD]
[TD] 06:30[/TD]
[TD] 08 [/TD]
[TD] 20:30[/TD]
[TD] 08 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 03:30[/TD]
[TD] 08 [/TD]
[TD] 08:30[/TD]
[TD] 08 [/TD]
[TD] 02:30[/TD]
[TD] 08 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 13:00[/TD]
[TD] 09 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 21:00[/TD]
[TD] 09 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 04:00[/TD]
[TD] 09 [/TD]
[TD] 09:00[/TD]
[TD] 09 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 13:30[/TD]
[TD] 10 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 21:30[/TD]
[TD] 10 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 04:30[/TD]
[TD] 10 [/TD]
[TD] 09:30[/TD]
[TD] 10 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 14:00[/TD]
[TD] 11 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 22:00[/TD]
[TD] 11 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 05:00[/TD]
[TD] 11 [/TD]
[TD] 10:00[/TD]
[TD] 11 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 14:30[/TD]
[TD] 12 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 22:30[/TD]
[TD] 12 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 05:30[/TD]
[TD] 12 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 23:00[/TD]
[TD] 13 [/TD]
[TD][/TD]
[TD][/TD]
[TD] 06:00[/TD]
[TD] 13 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 06:30[/TD]
[TD] 14 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 23:00[/TD]
[TD] 47 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 23:30[/TD]
[TD] 48 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD]End Time[/TD]
[TD] 07:38[/TD]
[TD][/TD]
[TD] 14:30[/TD]
[TD][/TD]
[TD] 06:45[/TD]
[TD][/TD]
[TD] 23:00[/TD]
[TD][/TD]
[TD] 07:30[/TD]
[TD][/TD]
[TD] 23:59[/TD]
[TD][/TD]
[TD] 10:29[/TD]
[TD][/TD]
[TD] 02:31[/TD]
[TD][/TD]
[TD] 01:01[/TD]
[TD][/TD]
[TD] 23:59[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]Result[/TD]
[TD] 05 [/TD]
[TD][/TD]
[TD] 12 [/TD]
[TD][/TD]
[TD] 08 [/TD]
[TD][/TD]
[TD] 13 [/TD]
[TD][/TD]
[TD] 06 [/TD]
[TD][/TD]
[TD] 48 [/TD]
[TD][/TD]
[TD] 11 [/TD]
[TD][/TD]
[TD] 08 [/TD]
[TD][/TD]
[TD] 05 [/TD]
[TD][/TD]
[TD] 00 [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*********[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]*****[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[sheetspread]

count how many 30 minute blocks are between (and including) those times?

shouldn't the expected result for the first example be 8? And the last example be 6?

That's what I was wondering too

=SUMPRODUCT(--(RIGHT(ROW(INDIRECT(ROUND(IF(B1,B1*24,0.5),2)*100&":"&
ROUND(((B52 < B1)+B52)*24,2)*100)),2)={"00","50"}))+(B1=0)

This might be the best one if the last line should return zero:

Excel 2010
	A	B	C	D	E
1	5:30	7:38	5	5	5
2	3:00	6:45	8	8	8
3	4:15	11:00	14	14	14
4	4:45	7:30	6	6	6
5	5:30	5:30	1	1	1
6	5:30	6:00	2	2	2
7	5:30	6:30	3	3	3
8	5:30	7:00	4	4	4
9	5:30	7:30	5	5	5
10	8:55	14:30	12	12	12
11	0:00	23:59	48	48	48
12	23:31	23:59	-1.8E-15	1	0
Sheet2 (2)

Cell Formulas
Range	Formula
C1	=1+((FLOOR(B1,"00:30")-CEILING(A1,"00:30"))/"00:30")
D1	=INT((B1-A1)*48)+1
E1	=SUMPRODUCT(--(RIGHT(ROW(INDIRECT(ROUND(IF(A1,A1*24,0.5),2)*100&":"&ROUND(((B1 < A1)+B1)*24,2)*100)),2)={"00","50"}))+(A1=0)