Count If Now between 15 min ago.

[rniles6654]

Need some big help. I did struggle for last few days, and couldn't figure it out.

I am looking for count from time to time from two cells. I made one cell =NOW() and other cell 00:15:00, so I did minus each other so we have other cell show 15 min earlier.

D5 - "=D7-D6"
D6 - "00:15:00"
D7 - "=Now()"

I did create other sheet called "Data" where stored all data in there. I did
=COUNTIFS(Data!B:B,">=02:30:00",Data!B:B,"<=2:52:00")
for test purposed and it work. It is count from 2:30:00 to 2:52:00.

What I am trying to do is... Replace "02:30:00" to D5, and replace "02:52:00" to D7. So that way I can refresh data and it will showed count last 15 min from now. It will lead me to create speedometer or gauges to see what happening on live. Seem it is not working that way.

Let me know how it make work.

Thank you!
Richard

 

[etaf]

=COUNTIFS(Data!B:B,">="&D5,Data!B:B,"<="&D7)

or if you dont want to use other cells
=COUNTIFS(Data!B:B,">="&NOW()-TIME(0,15,0),Data!B:B,"<="&NOW())

 

[rniles6654]

=COUNTIFS(Data!B:B,">="&D5,Data!B:B,"<="&D7)

or if you dont want to use other cells
=COUNTIFS(Data!B:B,">="&NOW()-TIME(0,15,0),Data!B:B,"<="&NOW())

---

I did copied your both suggested. I still get zero after I added data "5:23:31 PM" as soon as I refresh. I expect to count one but seem it not work on my end?

I did uploaded OneDrive, and take look at it.

https://1drv.ms/x/s!AuC827sxtSXEgu9Z4XhHSqChL6nbbQ

Let me know, Thank you for quick respond Etaf

 

[etaf]

i added at the bottomn of your data
=now()
=now(-time(0,13,0)

and i get a count of two

Also etaf#1 and 2 are the same

https://www.dropbox.com/s/kww61nobpgcva81/Countif_test_etaf.xlsx?dl=0

i added conditional formatting on column B data - to turn green if match

 

[etaf]

you column B does not have a date
actually its zero
0.xxxxxx
which is 1/1/1900

now()
also produces the date
so we need to get rid of the integer part of the time value and bring it back to zero

=now() - trunc(now())

so the formula we need is

=COUNTIFS(Data!B:B,">="&(NOW()-TRUNC(NOW()))-TIME(0,15,0),Data!B:B,"<="&(NOW()-TRUNC(NOW())))

and in D5 and D7 you need to change the formula
to also trunc

see the uploaded file again
working

=COUNTIFS(Data!B:B,">="&(NOW()-TRUNC(NOW()))-TIME(0,15,0),Data!B:B,"<="&(NOW()-TRUNC(NOW())))

 

[rniles6654]

you column B does not have a date
actually its zero
0.xxxxxx
which is 1/1/1900

now()
also produces the date
so we need to get rid of the integer part of the time value and bring it back to zero

=now() - trunc(now())

so the formula we need is

=COUNTIFS(Data!B:B,">="&(NOW()-TRUNC(NOW()))-TIME(0,15,0),Data!B:B,"<="&(NOW()-TRUNC(NOW())))

and in D5 and D7 you need to change the formula
to also trunc

see the uploaded file again
working

=COUNTIFS(Data!B:B,">="&(NOW()-TRUNC(NOW()))-TIME(0,15,0),Data!B:B,"<="&(NOW()-TRUNC(NOW())))

---

It's work well! Awesome! thank you very much for you help!

 

[etaf]

you are welcome