Improve company productivity with a Business Account.Sign Up

x
?
Solved

Only do this IF

Posted on 2014-02-27
7
Medium Priority
?
197 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 35

Accepted Solution

by:
Rob Henson earned 2000 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 93

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Closing Comment

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

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 35

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

606 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