Hi,

The reason the cell is showing "1/1/1900 1:00:00 PM" is because the cell value is 24 hours and higher (in this case, it's 37 hours, therefore the cell is basically showing 1 day 13 hours), RIGHT function won't work because the Real value in the cells is actually 1.541666667 formatted as DATE TIME.

If you don't care about the Day part of the value (anything 24 hours and above), you can add the MOD function in your formula like this:

MOD('Front End'!B$25:B$72,1)

MOD('Front End'!C$25:C$72,1)

Don't know the rest of your data, so don't know if this will work for you, just a suggestion if you want to ignore the day part of the values.

Edit: Just to show you what your modified formula will look like:

=SUMPRODUCT(--(A11>=MOD('Front End'!B$25:B$72,1)),--(MOD('Front End'!C$25:C$72,1)>=A11+"00:15"))+SUMPRODUCT(--(A11>='Combo EE'!B$36:B$69),--('Combo EE'!C$36:C$69>=A11+"00:15"))