[Solved] Calucating the total number of cells in a row that are less than 16 hours [closed]

by


Assuming that

  • column A of sheet1 are date/times, and not text, and
  • you are attempting to find how many date/times are more than 16 hours old

then you simply want to place the following formula in sheet2!F7:

=COUNTIF(sheet1!A:A,"<"&NOW()-0.75)

(16 hours is 0.75 of a day.)

If the data in column A is text, then it gets slightly harder as you have to ensure that Excel does not treat the NOW()-0.75 as a date/time. You can trick it by adding an extraneous character to the end of the formatted date/time so that it no longer appears to be valid, e.g.

=COUNTIF(sheet1!A:A,"<"&TEXT(NOW()-0.75,"yyyy/mm/dd hh:mm:ss")&"#")

worked for me. (The extra "#" at the end forced it to no longer be a valid date/time string.)

4

solved Calucating the total number of cells in a row that are less than 16 hours [closed]