Sumifs Hours Worked Between Two Dates and Times with Multiple Criteria

[bradyman97]

Below is a small sample of my spreadsheet. I'm trying to come up with a formula that adds the total hours worked between two dates, and the times are between 2200 and 0600, and if my code is Overtime 1.5. Formula result should be 16 hours.


(A1) Start Date "01/01/2024"
(A2) End Date "01/04/2024"
(A3) Code Name "Overtime 1.5"
(C1) Total Hours for the Code

DATE	RATE	CODE	START	END	TOTAL HOURS
01/01/2024	33.67	Normal Hours	14:00	22:00	8.00
01/01/2024	50.51	Overtime 1.5	22:00	06:00	8.00
01/02/2024	33.67	Normal Hours	14:00	22:00	8.00
01/03/2024	50.51	Overtime 1.5	06:00	14:00	8.00
01/03/2024	33.67	Normal Hours	14:00	22:00	8.00
01/04/2024	33.67	Normal Hours	14:00	22:00	8.00
01/04/2024	50.51	Overtime 1.5	22:00	06:00	8.00

Thank you for your time

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	RATE	CODE	START	END	TOTAL HOURS
2	01/01/2024	33.67	Normal Hours	14:00	22:00	8		01/01/2024	16
3	01/01/2024	50.51	Overtime 1.5	22:00	06:00	8		04/01/2024
4	02/01/2024	33.67	Normal Hours	14:00	22:00	8
5	03/01/2024	50.51	Overtime 1.5	06:00	14:00	8
6	03/01/2024	33.67	Normal Hours	14:00	22:00	8
7	04/01/2024	33.67	Normal Hours	14:00	22:00	8
8	04/01/2024	50.51	Overtime 1.5	22:00	06:00	8
9
Sheet6

Cell Formulas
Range	Formula
I2	I2	=SUMIFS(F:F,A:A,">="&H2,A:A,"<="&H3,C:C,"overtime 1.5",D:D,TIME(22,0,0),E:E,TIME(6,0,0))

 

[bradyman97]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	RATE	CODE	START	END	TOTAL HOURS
2	01/01/2024	33.67	Normal Hours	14:00	22:00	8		01/01/2024	16
3	01/01/2024	50.51	Overtime 1.5	22:00	06:00	8		04/01/2024
4	02/01/2024	33.67	Normal Hours	14:00	22:00	8
5	03/01/2024	50.51	Overtime 1.5	06:00	14:00	8
6	03/01/2024	33.67	Normal Hours	14:00	22:00	8
7	04/01/2024	33.67	Normal Hours	14:00	22:00	8
8	04/01/2024	50.51	Overtime 1.5	22:00	06:00	8
9
Sheet6

Cell Formulas
Range	Formula
I2	I2	=SUMIFS(F:F,A:A,">="&H2,A:A,"<="&H3,C:C,"overtime 1.5",D:D,TIME(22,0,0),E:E,TIME(6,0,0))

I see the formula works on your sheet but I can't figure out why it's not working on my sheet.


=SUMIFS(Timesheet[TOTAL HOURS],Timesheet[DATE],">="&'Pay_Statement (2)'!B2,Timesheet[DATE],"<="&'Pay_Statement (2)'!B3,Timesheet

,"Overtime 1.5",Timesheet[START TIME],TIME(22,0,0),Timesheet[END TIME],TIME(6,0,0))

 

[Fluff]

In what way isn't it working?

 

[bradyman97]

I have my date range from 12/25/2022 to 1/7/2023 and on 1/6/2023 I worked overtime from 22:00 to 0600 and it's not calculating that date. IF I change say 1/3/2023 to overtime from 22:00 to 06:00 it calculates it right but for some reason it's not on 1/6/2023

 

[bradyman97]

I have my date range from 12/25/2022 to 1/7/2023 and on 1/6/2023 I worked overtime from 22:00 to 0600 and it's not calculating that date. IF I change say 1/3/2023 to overtime from 22:00 to 06:00 it calculates it right but for some reason it's not on 1/6/2023

It's giving me a result of zero

 

[Fluff]

Do any of the dates also include the time?

 

[bradyman97]

Do any of the dates also include the time?

No just dates and the times are just times

 

[Fluff]

In that case make sure that all the dates are real dates & not text. The same goes for the times.

 

[bradyman97]

how do I upload my spreadsheet for you to look at