Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-30-2014, 12:21 PM
cangelis cangelis is offline Greater than today at a specific time Windows 7 64bit Greater than today at a specific time Office 2010 64bit
Novice
Greater than today at a specific time
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default Greater than today at a specific time

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.
Reply With Quote
  #2  
Old 10-01-2014, 06:28 AM
gebobs gebobs is offline Greater than today at a specific time Windows 7 64bit Greater than today at a specific time Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

">" & today()+7/24
Reply With Quote
  #3  
Old 10-01-2014, 07:03 AM
cangelis cangelis is offline Greater than today at a specific time Windows 7 64bit Greater than today at a specific time Office 2010 64bit
Novice
Greater than today at a specific time
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 10-01-2014, 08:13 AM
gebobs gebobs is offline Greater than today at a specific time Windows 7 64bit Greater than today at a specific time Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

That's the criteria you need to put in your countifs statement.
Reply With Quote
  #5  
Old 10-01-2014, 08:19 AM
cangelis cangelis is offline Greater than today at a specific time Windows 7 64bit Greater than today at a specific time Office 2010 64bit
Novice
Greater than today at a specific time
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 10-01-2014, 08:33 AM
gebobs gebobs is offline Greater than today at a specific time Windows 7 64bit Greater than today at a specific time Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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)
Reply With Quote
  #7  
Old 10-01-2014, 08:48 AM
cangelis cangelis is offline Greater than today at a specific time Windows 7 64bit Greater than today at a specific time Office 2010 64bit
Novice
Greater than today at a specific time
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you review status in Project from a previous time prior to "Today" StevieMar Project 3 04-30-2014 09:42 AM
Auto Forward Emails - Specific Time Periods Only Dav Outlook 0 06-22-2011 12:03 PM
how to make the task starts next day if no time enough today(task can not be split) duraid Project 1 10-25-2010 08:31 AM
outlook 2000 pst greater than 2gb bbxrider Outlook 0 09-25-2010 02:37 PM
Greater than today at a specific time If A1 greater than or equal to 1 Then A3 = A2*A1 brians Excel 5 03-09-2010 12:04 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft