Solved

Only do this IF

Posted on 2014-02-27
7
186 Views
Last Modified: 2014-02-28
How do I add an If statement to this that will only run this code if current time is before 1pm? and do not run if after one pm?  I only want it to execute for the morning.

'Delete old CDPSRECRPT ... sheet tab
    Windows("BruceShortageRpt.xlsm").Activate
    Application.DisplayAlerts = False
    Sheets("CDPSRECRPT-Yesterday").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("CDPSRECRPT").Select
    Sheets("CDPSRECRPT").Name = "CDPSRECRPT-Yesterday"

Open in new window

0
Comment
Question by:RWayneH
  • 3
  • 3
7 Comments
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39892047
Amend to:

If Time < 13 / 24 Then
    Windows("BruceShortageRpt.xlsm").Activate
    Application.DisplayAlerts = False
    Sheets("CDPSRECRPT-Yesterday").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("CDPSRECRPT").Select
    Sheets("CDPSRECRPT").Name = "CDPSRECRPT-Yesterday"
End If

Thanks
Rob H
0
 

Author Comment

by:RWayneH
ID: 39892059
what does the 13 / 24 mean?    13 is 1pm and the 24 is the type of clock to use?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39892110
(No points, please)

Yes, that is precisely what it's doing.

You could also do:

If Hour(Now) < 13

or any number of other ways.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Closing Comment

by:RWayneH
ID: 39892321
Cool!  Thanks.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39895121
Apologies if speaking to the converted but might be worth noting how excel deals with date and time.

Date and time are recorded behind the scenes in excel as a serial number.

Date is number of days since 01/01/1900

Today (28 feb 2014) is 41698, test this by entering the formula =TODAY() into a cell and then format it as a number rather than date.

Time is a decimal portion of a day. So 12:00 noon would 0.5

So noon today (28 Feb 2014 12:00) is stored as 41698.5

Therefore knowing that and knowing that a day has 24 hours, 13:00 would be 13 hours into the 24 hence the 13/24 fraction of a day.

Thanks
Rob H
0
 

Author Comment

by:RWayneH
ID: 39895138
Does this apply to what day it is too?  So I could use things like, if today is Monday or today is Sunday?
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39895193
To an extent yes.

You can use the WEEKDAY(DATE,n) function to determine which day of the week a day is. The result of WEEKDAY is a number from 1 to 7.

The DATE refers to the date and the n refers to one of numerous parameters for determining how you want the weekday represented, ie whether day 1 of a week is Sunday or Monday. Check the online help for these.

Going further, you can use the FLOOR and CEILING functions to round a date down or up to a specific day of the week.

=FLOOR(Number,Factor)    

FLOOR rounds Number down to the previous Factor, so having the Number section as your date and Factor as 7 will round down to the previous Saturday. 01/01/1900 was a Sunday therefore every Saturday is a factor of 7.

So, the following:
=FLOOR(Date,7)+2
will round the date to the Monday of the same week.

Example:
="Week Commencing "&TEXT(FLOOR(TODAY(),7)+1,"dd-mmm")
Result:
"Week Commencing 24-Feb"

CEILING works the same but rounds up rather than down

=CEILING(Date,7)-1  will give the Friday of the same week.

Example:
="Week Ending "&TEXT(CEILING(TODAY(),7)+1,"dd-mmm")
Result:
"Week Ending 28-Feb"

Thanks
Rob H
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now