Greater than today at a specific time

[cangelis]

I am constructing a macro. I need one of the formula's to pull in data from a report that will count any entries on the report after 07:00 a.m. This will need to be done daily which is why I cannot hard code a specific date. I have tried:

>TODAY 07:00

The date and time are in one column and I do not want to split into two columns (text to columns).

This is the entire string:

Range("AI7").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS([DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C7:R[9994]C7,""success"",[DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C15:R[9994]C15,"">TODAY 07:00"")"

Everything else works.

[gebobs]

">" & today()+7/24

[cangelis]

Thank you. We are almost there. However, ">" & today()+7/24, returned a TRUE statement. I need this to be a count of records after 07:00.

[gebobs]

That's the criteria you need to put in your countifs statement.

[cangelis]

Thank you. I understand. What I don't understand is how to put it in the statement in order to return the results I need. What I have right now is:

Range("AI7").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS([DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C7:R[9994]C7,""success"",[DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C15:R[9994]C15," > " & today()+7/24"")"

Obviously it needs to go someplace else since I am only getting a TRUE instead of a number. I just don't know where.

[gebobs]

Gotta admit, my VBA is not what it once was. I take it ActiveCell.FormulaR1C1 requires a text argument. Forget about the result. When you run your code, what is the actual formula that is entered into A17?

It looks like you have a few extraneous characters. Try this...

Range("AI7").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS([DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C7:R[9994]C7,""success"",[DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C15:R[9994]C15,"">"" & TODAY()+7/24)"

When you run the code, the formula in A17 then should be:

=COUNTIFS([DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C7:R[9994]C7,"success",[DPA_ScheduledReport.csv]DPA_ScheduledReport!R[-4]C15:R[9994]C15,">" & TODAY()+7/24)

[cangelis]

Thank you. Your VBA skills are still very good. I think this will work. I am waiting for an updated report to really test this.