Solved

Only do this IF

Posted on 2014-02-27
7
185 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 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
(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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Closing Comment

by:RWayneH
Comment Utility
Cool!  Thanks.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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

by:Rob Henson
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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…

744 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

15 Experts available now in Live!

Get 1:1 Help Now