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]

If you convert col D to numbers & expand the number of decimals visible you will see that you have the wrong value

 

[bradyman97]

If you convert col D to numbers & expand the number of decimals visible you will see that you have the wrong value

How does that happen when i have 22:00 (format Time) in the cell and it says 10:00:00 PM in the formula bar

 

[Fluff]

Time is just a special type of cell format & does not change/affect the actual value in the cell.

 

[bradyman97]

Time is just a special type of cell format & does not change/affect the actual value in the cell.

Problem solved. Thank you for your help.

I had to enter my time as 10:00:00 PM and not as 22:00.

 

[Fluff]

Glad it's sorted & thanks for the feedback.