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]

You can upload it to a share site such as OneDrive, GoogleDrive mark for sharing & then post the link you are given here.

 

[bradyman97]

Here copy of my workbook. Go to tab sheet1

My Work Timesheet

Timesheet YEAR,RATE,CODE,START TIME,END TIME,TOTAL HOURS 2023, $ 33.67 ,NH (2-10),12/11/2022 14:00,12/11/2022 22:00,8.00,1.Must put colon between Hr/min 2023, $ 33.67 ,OT 1.5 (10-6),12/11/2022 22:00,12/12/2022 06:00,8.00,2.When you work a holiday put codes: NH (whatever shift) 8 hrs and H (.50-...

docs.google.com

 

[bradyman97]

Here is a copy of my workbook. It's under tab sheet1

My Work Timesheet

Timesheet YEAR,RATE,CODE,START TIME,END TIME,TOTAL HOURS 2023, $ 33.67 ,NH (2-10),12/11/2022 14:00,12/11/2022 22:00,8.00,1.Must put colon between Hr/min 2023, $ 33.67 ,OT 1.5 (10-6),12/11/2022 22:00,12/12/2022 06:00,8.00,2.When you work a holiday put codes: NH (whatever shift) 8 hrs and H (.50-...

docs.google.com

 

[Fluff]

Both those links are asking me to sign in. You need to mark it for sharing & then post that link you are given.

 

[bradyman97]

Try this one

My Work Timesheet

Timesheet YEAR,RATE,CODE,START TIME,END TIME,TOTAL HOURS 2023, $ 33.67 ,NH (2-10),12/11/2022 14:00,12/11/2022 22:00,8.00,1.Must put colon between Hr/min 2023, $ 33.67 ,OT 1.5 (10-6),12/11/2022 22:00,12/12/2022 06:00,8.00,2.When you work a holiday put codes: NH (whatever shift) 8 hrs and H (.50-...

docs.google.com

 

[bradyman97]

If you change the start date in H2 to 12/11/2022 and H3 to 1/7/2023 it gives the result of 16 and the correct result should be 64

 

[Fluff]

Thanks for that, but there is nothing in col C of Timesheet that has "overtime 1.5". Hence you get 0

 

[bradyman97]

Thanks for that, but there is nothing in col C of Timesheet that has "overtime 1.5". Hence you get 0

It is under the tab Sheet1

 

[Fluff]

There is only one row on that sheet that appears to match the criteria, which is row 45 but the time is not exactly 22:00:00.
It is 0.916666666664241 rater than 0.916666666666667

 

[bradyman97]

There is only one row on that sheet that appears to match the criteria, which is row 45 but the time is not exactly 22:00:00.
It is 0.916666666664241 rater than 0.916666666666667

I don't understand