Number of daytime hours falling between two date ranges

[sparkle999]

Hello, I am attempting to prepare a report for our local village hall, we use a google calendar for logging booked events and I want to analyse bookings. Having exported the data from google calendar, I have a data sheet in excel which has one row per event, with some information about the event including start and end date/timestamps, duration in hours, and other things.

I would like to compute for each event how many hours of that booking fall between 8am and 6pm (daytime), and another calculation for hours that fall between 6pm and midnight (evening).
Some events can last three days or so, many events are just for a few hours.

Can you please help me with a formula that will cater for multiple days?
I have tried an AI engine and that just went round in circles, it couldn't find a solution that actually worked!
I searched here and saw a solution based on NETWORKDAYS but having looked into that I don't care whether it's a weekend or holiday, that doesn't matter.
I tried editing the formula to use DAYS instead of NETWORKDAYS but then it only seemed to work on whole days (even when multiplying out to get hours I was getting 0 for most events), and I am looking for an answer in hours.

It was this formula: =((DAYS(A2,B2)-1)*(DAYEND-DAYSTART) +IF(DAYS(B2,B2),MEDIAN(MOD(B2,1),DAYEND,DAYSTART),DAYEND) -MEDIAN(DAYS(A2,A2)*MOD(A2,1),DAYEND,DAYSTART))

I'd be grateful if anyone could point me in the right direction.
Thanks
Nige

 

[sparkle999]

Thought it would be worth showing a mockup, I have manually calculated the hours, and am hoping you can help with a formula that will give the same results.
The challenging one is event 3....
Thanks again

 

[Cubist]

Try:

Book1
	C	D	E	F
1
2	Start	Finish	Hours (8am - 6pm)	Hours (6pm - 12AM)
3	5/19/19 10:00 AM	5/19/19 3:00 PM	5	0
4	10/6/23 6:00 PM	10/6/23 10:00 PM	0	4
5	7/4/25 12:00 AM	7/7/25 12:00 AM	30	18
Sheet8

Cell Formulas
Range	Formula
E3:E5	E3	=LET(m,MOD(SEQUENCE((D3-C3)*24,,C3,1/24),1),SUM((m>=1/3)*(m<3/4)))
F3:F5	F3	=LET(m,MOD(SEQUENCE((D3-C3)*24,,C3,1/24),1),SUM((m=0)+(m>=3/4)*(m<=1)))

 

[sparkle999]

Dear Cubist, many thanks for taking the time and trouble to reply, much appreciated.
I think this works (apart from when the number of hours from start to finish is <1, that gives a #CALC error)
This is a big step forward for me, I need to study the formulae you have used to better understand what's going on, but suffice it to say, I'm very grateful!
Thanks
Nige

 

[Cubist]

(apart from when the number of hours from start to finish is <1, that gives a #CALC error)

What do you expect it to return in this case? 0 or 1?

 

[sparkle999]

What do you expect it to return in this case? 0 or 1?

Hmmm. If I was being pedantic I would go for <0.5 hours = zero, and >=0.5 hours then 1.
But this is for an analysis of utilisation of a village hall, so I think zero would be fine
Thanks again

 

[Cubist]

<0.5 hours = zero, and >=0.5 hours then 1.

Try:

Book1
	C	D	E	F
1
2	Start	Finish	Hours (8am - 6pm)	Hours (6pm - 12AM)
3	5/19/19 10:00 AM	5/19/19 3:00 PM	5	0
4	10/6/23 6:00 PM	10/6/23 10:00 PM	0	4
5	7/4/25 12:00 AM	7/8/25 1:00 AM	40	25
6	7/4/25 8:00 AM	7/4/25 8:15 AM	0	0
7	7/4/25 8:00 AM	7/4/25 8:50 AM	1	0
8	7/4/25 6:00 PM	7/4/25 6:15 PM	0	0
9	7/4/25 6:00 PM	7/4/25 6:50 PM	0	1
Sheet8

Cell Formulas
Range	Formula
E3:E9	E3	=LET(a,C3,b,D3,m,MOD(IF(b-a<1/24,b,SEQUENCE((b-a)*24,,a,1/24)),1),(MOD(b-a,1)>1/48)*SUM((m>=1/3)*(m<3/4)))
F3:F9	F3	=LET(a,C3,b,D3,m,MOD(IF(b-a<1/24,b,SEQUENCE((b-a)*24,,a,1/24)),1),(MOD(b-a,1)>1/48)*SUM((m=0)+(m>=3/4)))

 

[sparkle999]

Try:

Book1
	C	D	E	F
1
2	Start	Finish	Hours (8am - 6pm)	Hours (6pm - 12AM)
3	5/19/19 10:00 AM	5/19/19 3:00 PM	5	0
4	10/6/23 6:00 PM	10/6/23 10:00 PM	0	4
5	7/4/25 12:00 AM	7/8/25 1:00 AM	40	25
6	7/4/25 8:00 AM	7/4/25 8:15 AM	0	0
7	7/4/25 8:00 AM	7/4/25 8:50 AM	1	0
8	7/4/25 6:00 PM	7/4/25 6:15 PM	0	0
9	7/4/25 6:00 PM	7/4/25 6:50 PM	0	1
Sheet8

Cell Formulas
Range	Formula
E3:E9	E3	=LET(a,C3,b,D3,m,MOD(IF(b-a<1/24,b,SEQUENCE((b-a)*24,,a,1/24)),1),(MOD(b-a,1)>1/48)*SUM((m>=1/3)*(m<3/4)))
F3:F9	F3	=LET(a,C3,b,D3,m,MOD(IF(b-a<1/24,b,SEQUENCE((b-a)*24,,a,1/24)),1),(MOD(b-a,1)>1/48)*SUM((m=0)+(m>=3/4)))

That's perfect, thank you! Many thanks from a small village hall charity in the UK

 

[Cubist]

You're welcome.