Date Time

[Johnboy]

On Sheet1 column A is date time formatted "dd-mmm-yy hh:mm:ss", Sheet2 contains the following formula:

=SUMPRODUCT(--(Sheet1!A7:A50000>=I34),--(Sheet1!A7:A50000<=K34),--(Sheet1!J17:J50010="a"))

Which works, but the dates in on Sheet2 I34 and K34 automatically assume 12:00:00 PM and end up missing any entry before 12 PM on start date and after 12 PM on end date. I would like to have the formula to reflect I34 and time 00:00:01 and k34 and time 23:59:59 without having to put hours in cells I34 and K34.

Thanks
Johnboy

 

[Richard Schollar]

They will automatically assume 12am times on those dates if you don't include any h:m:s. So maybe you should set it as:

=SUMPRODUCT(--(Sheet1!A7:A50000>=I34),--(Sheet1!A7:A50000<=K34+1),--(Sheet1!J17:J50010="a"))

 

[MrKowz]

perhaps:

=SUMPRODUCT(--(Sheet1!A7:A50000>=(I34+TIMEVALUE("00:01")),--(Sheet1!A7:A50000<=(K34-TIMEVALUE("00:01")),--(Sheet1!J17:J50010="a"))

 

[Johnboy]

Thanks, both solutions will work.

Johnboy