Date Time

Johnboy

Board Regular
Joined
Oct 25, 2004
Messages
144
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
perhaps:

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

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,477
Members
409,702
Latest member
thmoriarty

This Week's Hot Topics

Top