Solved

# Only do this IF

Posted on 2014-02-27
185 Views
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
Sheets("CDPSRECRPT-Yesterday").Select
ActiveWindow.SelectedSheets.Delete
Sheets("CDPSRECRPT").Select
Sheets("CDPSRECRPT").Name = "CDPSRECRPT-Yesterday"
``````
0
Question by:RWayneH
• 3
• 3

LVL 31

Accepted Solution

Rob Henson earned 500 total points
Amend to:

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

Thanks
Rob H
0

Author Comment

what does the 13 / 24 mean?    13 is 1pm and the 24 is the type of clock to use?
0

LVL 92

Expert Comment

Yes, that is precisely what it's doing.

You could also do:

If Hour(Now) < 13

or any number of other ways.
0

Author Closing Comment

Cool!  Thanks.
0

LVL 31

Expert Comment

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

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 31

Expert Comment

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

This collection of functions covers all the normal rounding methods of just about any numeric value.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

#### Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!