Sumproduct/Sum Date Range down to second

Hello,

I am using these two formulas, and I have having an issue with returning date/time for the last second of the hour.

For example if the time is 12:59:59, it's ignoring it. If I manually change the time to 12:59:58 it returns the data.

I am not sure why, as <=B40 time is 12:59:59 for this example.

I can create a sample sheet if it is helpful.

=SUMPRODUCT(--((WEEKDAY(Table1[Unit Date])=INDEX(SEQUENCE(7),MATCH($A$38,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)))*((MOD(Table1[Unit Date],1)>=$A40)*((MOD(Table1[Unit Date],1)<=$B40)*(Table1[Unit Date]>0)*(Table1[Transaction Type])=5)*(Table1[Status]="Completed"))))

=IFERROR(SUM(IF(WEEKDAY(Table1[Unit Date])=INDEX(SEQUENCE(7),MATCH($A$38,{"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"},0)),IF(MOD(Table1[Unit Date],1)>=$A40,IF(MOD(Table1[Unit Date],1)<=$B40,IF(Table1[Transaction Type]=5,IF(Table1[Status]="Completed",Table1[Requested / Deposited Amount])))))),0)

Thanks for any help,
Nick

Hello, here is a sample.

Thanks,
Nick

Sheet2

M4=SUMPRODUCT((MOD(Table1[Unit Date],1)>=Sheet2!$A4)*(MOD(Table1[Unit Date],1)<=ROUND(Sheet2!$B4,10))*(Table1[Transaction Type]=5)*(TEXT(INT(Table1[Unit Date]),"dddd")=Sheet2!$A$2))

Copy down

N4=SUMPRODUCT((MOD(Table1[Unit Date],1)>=Sheet2!$A4)*(MOD(Table1[Unit Date],1)<=ROUND(Sheet2!$B4,10))*(Table1[Transaction Type]=5)*(Table1[Requested / Deposited Amount])*(TEXT(INT(Table1[Unit Date]),"dddd")=Sheet2!$A$2))

copy down

Thank you Caracalla, I used your formulas and added more data to the sample (attached) and it is still off a bit.

Sheet2

M4=SUMPRODUCT((MOD(Table1[Unit Date],1)>=Sheet2!$A4)*(MOD(Table1[Unit Date],1)<=ROUND(Sheet2!$B4,5))*(Table1[Transaction Type]=5)*(TEXT(INT(Table1[Unit Date]),"dddd")=Sheet2!$A$2))

Copy down

N4=SUMPRODUCT((MOD(Table1[Unit Date],1)>=Sheet2!$A4)*(MOD(Table1[Unit Date],1)<=ROUND(Sheet2!$B4,5))*(Table1[Transaction Type]=5)*(Table1[Requested / Deposited Amount])*(TEXT(INT(Table1[Unit Date]),"dddd")=Sheet2!$A$2))

copy down

Thanks Caracalla, on the sample I attached that worked, but when I input it into my main spreadsheet, the totals were still off.

I did end up just making a macro to adjust the 59:59 to 59:58. Not ideal, but it is just a small discrepancy.

I appreciate everyone's feedback, the report I pull has the data as seen in the samples, and this is the best work around.

This did give me a lot of insight into why this does happen.

Thanks,
Nick