Extracting Time Spent on a Project in a Given Month

[G Wiz]

Is there a way to get excel to use data validation to select a month, and it give results in a cell that picks out only the entries based on the month selected.

The dates are currently entered in this format:

16/08/2021

21/08/2021

29/08/2021

30/08/2021

04/09/2021

07/09/2021

11/09/2021

11/09/2021

11/09/2021

12/09/2021

13/09/2021

13/09/2021

13/09/2021

13/09/2021

This would also need to be based on another data validation result.

I'd like to have a sheet calculate the amount of hours worked on a project in a selected month:

Project #	Project Title	Month	Hours
ECA	ECA abcdefg	January

The hours and month would need to refer back to the main timesheet entries:

Date	Start	Finish	Hours	Project #	Project Title
16/08/2021	21:15	22:30	01:15	ECA	ECA abcdefg
21/08/2021	13:30	17:00	03:30	ECA	ECA abcdefg
29/08/2021	20:30	23:00	02:30	ECA	ECA abcdefg
30/08/2021	10:30	11:45	01:15	ECA	ECA abcdefg
04/09/2021	11:30	13:30	02:00	ECA	ECA abcdefg
07/09/2021	21:30	22:30	01:00	ECA	ECA abcdefg
11/09/2021	14:45	15:30	00:45	ECA	ECA abcdefg
11/09/2021	16:30	16:45	00:15	ECA	ECA abcdefg
11/09/2021	18:00	19:00	01:00	ECA	ECA abcdefg
12/09/2021	20:30	23:15	02:45	ECA	ECA abcdefg
13/09/2021	07:30	08:30	01:00	ECA	ECA abcdefg

Is this achievable?

 

[Eric W]

I'd recommend using the 1st of the month in your selection criteria (cell J3 below) because you can include the year that way. Then you can do something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Start	Finish	Hours	Project #	Project Title
2	16/08/2021	21:15	22:30	1:15	ECA	ECA abcdefg		Project #	Project Title	Month	Hours
3	21/08/2021	13:30	17:00	3:30	ECA	ECA abcdefg		ECA	ECA abcdefg	1/09/2021	8:45
4	29/08/2021	20:30	23:00	2:30	ECA	ECA abcdefg		ECA	ECA abcdefg	September	8:45
5	30/08/2021	10:30	11:45	1:15	ECA	ECA abcdefg
6	4/09/2021	11:30	13:30	2:00	ECA	ECA abcdefg
7	7/09/2021	21:30	22:30	1:00	ECA	ECA abcdefg
8	11/09/2021	14:45	15:30	0:45	ECA	ECA abcdefg
9	11/09/2021	16:30	16:45	0:15	ECA	ECA abcdefg
10	11/09/2021	18:00	19:00	1:00	ECA	ECA abcdefg
11	12/09/2021	20:30	23:15	2:45	ECA	ECA abcdefg
12	13/09/2021	7:30	8:30	1:00	ECA	ECA abcdefg
13
Sheet46

Cell Formulas
Range	Formula
K3	K3	=SUMIFS(D2:D12,A2:A12,">="&J3,A2:A12,"<="&EOMONTH(J3,0),E2:E12,H3)
K4	K4	=SUMPRODUCT(D2:D12,--(E2:E12=H4),--(TEXT(A2:A12,"mmmm")=J4))

If you want to stick with the month only, you can try the K4 formula.

 

[G Wiz]

Right, OK. Ill give that a go! Thanks.
Will update on progress...