Using EOMONTH to calculate number of hours between dates

[Allen_Mead]

Hi, I need to calculate the number of CPD hours I have worked over the previous 24 months (or in fact any number of months) in a rolling period in total. This also needs to be split down into 2 12 month periods ie. the immediate previous 12 months and the 12 month period behand that. i.e. 31/12/2024 - 31/01/2024 and 31/12/2023 - 31/01/2023.

In the attached spreadsheet, there is a summary sheet with 3 summaries and a data tab.

Summary 1 will let me put in any number of months and will calculate the number of hours over that total period, perfect.
Summary 2 will let me calculate the previous 12 month period, perfect.
Summary 3 will let me calculate the previous 12 month period to Summary 2 but, there's an error somewhere as there are not 13 hours in this time period and this is getting me stuck.

Hopefully the above makes sense. More that welcome to adjust the summary page to something a little less clumsy. Data Tab information included in the second post on the thread.

CPD Chart.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2		Summary 1								Summary 2								Summary 3
3
4					Get CPD hours for the last		24	Months					Get CPD hours for the last		12	Months					Get CPD hours for the previous		24	Months
5
6							Date Range								Date Range								Date Range
7
8					Start		31/01/2023						Start		31/01/2024						Start		31/01/2023
9
10					End		31/12/2024						End		31/12/2024						End		31/12/2023
11
12					Number of CPD hours in a rolling 24 month period		13						Number of CPD hours in a rolling 12 month period		3						Number of CPD hours in a rolling 24 month period		13
13
14
15
16					Total number of CPD hours		20						Total number of CPD hours		0						Total number of CPD hours		0
17
18
19
Summary

Cell Formulas
Range	Formula
G8,W8,O8	G8	=EOMONTH(TODAY(),-(G4-1))
G10	G10	=EOMONTH(TODAY(),0)
O10,W10	O10	=EOMONTH(TODAY(),-(O4-12))
E12,U12,M12	E12	="Number of CPD hours in a rolling " & TEXT(G4,"##") &" month period"
G12,W12,O12	G12	=SUM(XLOOKUP(G8,'CPD Data'!$C$2:$FB$2,'CPD Data'!$C$3:$FB$340,0):XLOOKUP(EOMONTH(TODAY(),0),'CPD Data'!$C$2:$FB$2,'CPD Data'!$C$3:$FB$340,0))
G16,W16,O16	G16	='CPD Data'!B341

 

[Allen_Mead]

CPD Chart.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH
1
2	CPD Event	Date	31/01/2018	28/02/2018	31/03/2018	30/04/2018	31/05/2018	30/06/2018	31/07/2018	31/08/2018	30/09/2018	31/10/2018	30/11/2018	31/12/2018	31/01/2019	28/02/2019	31/03/2019	30/04/2019	31/05/2019	30/06/2019	31/07/2019	31/08/2019	30/09/2019	31/10/2019	30/11/2019	31/12/2019	31/01/2020	28/02/2020	31/03/2020	30/04/2020	31/05/2020	30/06/2020	31/07/2020	31/08/2020	30/09/2020	31/10/2020	30/11/2020	31/12/2020	31/01/2021	28/02/2021	31/03/2021	30/04/2021	31/05/2021	30/06/2021	31/07/2021	31/08/2021	30/09/2021	31/10/2021	30/11/2021	31/12/2021	31/01/2022	28/02/2022	31/03/2022	30/04/2022	31/05/2022	30/06/2022	31/07/2022	31/08/2022	30/09/2022	31/10/2022	30/11/2022	31/12/2022	31/01/2023	28/02/2023	31/03/2023	30/04/2023	31/05/2023	30/06/2023	31/07/2023	31/08/2023	30/09/2023	31/10/2023	30/11/2023	31/12/2023	31/01/2024	29/02/2024	31/03/2024	30/04/2024	31/05/2024	30/06/2024	31/07/2024	31/08/2024	30/09/2024	31/10/2024	30/11/2024	31/12/2024
3	Protecting High-rise Residential Buildings from Fire	31/03/2021																																							1
4	Built Environment Monthly Webinar - The Golden Thread of information and what it eans for the sector	21/04/2021																																								1
5	Bridging the gap between fire and emergency lighting systems	10/06/2021																																										1
6	Bureau Veritas - Building Safety Bill Seminar	17/09/2021																																													1
7	Class 0 Conumdrum	13/04/2022																																																				1
8	The Key Essentials of Water Mist Fire Protection	01/07/2022																																																							1
9	Asbestos Awareness UKI - Module 1 AECOM University	17/08/2022																																																								1
10	BSI/IFE Collaborative Reporting for Safer Structures	23/08/2022																																																								1
11	Design of car park smoke contorl systems	04/10/2022																																																										1
12	BSI/IFE Understanding Planning Gateway One (PGO): What you need to know	28/02/2023																																																														1
13	Fire Safety requirements and the role of the fire engineer through the gateways	27/06/2023																																																																		1
14	BSI/ IFE Fire Testing: Concepts, Principles and Innovation	26/09/2023																																																																					1
15	BSI/ IFE Evacuation lift design in fire engineering	31/10/2023																																																																						1
16	Fire Safety in Construction - What's Next?	21/11/2023																																																																							4
17	Coopers Fire CPD on Active Fire Curtain Barriers	12/12/2023
18	AECOM University - The Building Safety Act	15/12/2023																																																																								1
19	AECOM University - EUR&I Driving Awareness Course - FY24	20/12/2023																																																																								1
20	Bsi - Review of 2023 and looking forward to 2024	30/01/2024																																																																									1
21	Demystifying the Golden Thread of the Building Safety Act	01/02/2024																																																																										1
22	Residential Smoke Control Overview	19/12/2024																																																																																				1
CPD Data

 

[StephenCrump]

W12: =SUM(XLOOKUP(W8,'CPD Data'!$C$2:$FB$2,'CPD Data'!$C$3:$FB$340,0):XLOOKUP(EOMONTH(TODAY(),0),'CPD Data'!$C$2:$FB$2,'CPD Data'!$C$3:$FB$340,0))

should be

=SUM(XLOOKUP(W8,'CPD Data'!$C$2:$FB$2,'CPD Data'!$C$3:$FB$340,0):XLOOKUP(W10,'CPD Data'!$C$2:$FB$2,'CPD Data'!$C$3:$FB$340,0))

And your StartDate formula in W8 should be: =EOMONTH(TODAY(),-(O4+W4-1))