0

I am currently using the formula below to count occurrences between a date range where column BB is greater than 2000. "Year" is a variable from another cell. Column H is in date time format MM/DD/YY HH:MM.

=COUNTIFS('Heat Sumary'!$H:$H,">="&DATE(Year,10,1),'Heat Sumary'!$H:$H,"<"&DATE(Year,11,1),'Heat Sumary'!$BB:$BB,">2000")

How do I rewrite this formula to be between Oct 1st at 6pm and Nov 1st at 6pm instead of just the dates?

Xgrunt24
  • 25
  • 1
  • 4

1 Answers1

1

Add TIME(18,0,0) to each of the dates.

In other words, change the formula from...

=COUNTIFS('Heat Sumary'!$H:$H,">="&DATE(Year,10,1),'Heat Sumary'!$H:$H,"<"&DATE(Year,11,1),'Heat Sumary'!$BB:$BB,">2000")

to...

=COUNTIFS('Heat Sumary'!$H:$H,">="&(DATE(Year,10,1)+TIME(18,0,0)),'Heat Sumary'!$H:$H,"<"&(DATE(Year,11,1)+TIME(18,0,0)),'Heat Sumary'!$BB:$BB,">2000")
Bobby Orndorff
  • 3,265
  • 1
  • 9
  • 7
  • I was thinking that there could not be multiple criteria within a single criteria. Anyway...This works great! Thank you for your help. – Xgrunt24 Nov 11 '15 at 22:02
  • Excel stores dates and times as number values with the integer part representing a date and the decimal part representing a time. The integer part is the number of days since 1899-Dec-30. The decimal part is the fraction of a day. For example, DATE(2015,10,1) equals 42278 and TIME(18,0,0) equals 0.75. Thus, DATE(2015,10,1)+TIME(18,0,0) equals the number value 42278.75. – Bobby Orndorff Nov 12 '15 at 01:36