sum all hours between two dates and times

[Chris Turner]

I am using a formula to look at a table and sum all production hours from the previous day or 24 hours. Column B is the date in the table. Cells F2 and F3 have the formula =Today()-1 to sum all hours for the previous days date (i.e. yesterday).

=SUMIFS('Molding WC Log'!$F:$F,'Molding WC Log'!$A:$A,"="&'Molding Hours - Yesterday'!$A5,'Molding WC Log'!$J:$J,"="&'Molding Hours - Yesterday'!E$4,'Molding WC Log'!$B:$B,">="&$F$2,'Molding WC Log'!$B:$B,"<="&$F$3)

I just found out that the database starts to log the hours starting from 11:30 PM till 11:30 PM on the following day. This means my formula is not picking up the full 24 hours of production time. I want to add to the formula to allow it to pick up the hours (for example in the sample spreadsheet) between 11:30 PM on June 17 till 11:30 PM on June 18.

 

[vletm]

Chris Turner
Could You 'get' those date and time to same cell ... ? = Why needs two cells?
eg 17/06/18 11:30PM .. now calculated to cell E2
Then You would have one start time with date ...

 

[Chris Turner]

vietm. thanks for the feedback. I did not think about combining the time and date into one cell. It works perfectly. I truly appreciate your help and support to solve my problem.

 

[bosco_yip]

...... I did not think about combining the time and date into one cell. ............

Remain your form design and not to combine date and time into one cell,

try to use SUMPRODUCT () instead of SUMIFS (),

In B5, copied across and down :

=SUMPRODUCT((Table1[[Workcenter]:[Workcenter]]=$A5)*(Table1[[Status]:[Status]]=B$4)*((Table1[[Date]:[Date]]+Table1[[Time]:[Time]])>=($C$2+$D$2))*((Table1[[Date]:[Date]]+Table1[[Time]:[Time]])<=($C$3+$D$3)),Table1[[Hours]:[Hours]])

Regards
Bosco