Only do this IF

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

RWayneHAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
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
 
RWayneHAuthor Commented:
what does the 13 / 24 mean?    13 is 1pm and the 24 is the type of clock to use?
0
 
Patrick MatthewsCommented:
(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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
RWayneHAuthor Commented:
Cool!  Thanks.
0
 
Rob HensonFinance AnalystCommented:
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
 
RWayneHAuthor Commented:
Does this apply to what day it is too?  So I could use things like, if today is Monday or today is Sunday?
0
 
Rob HensonFinance AnalystCommented:
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
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.

All Courses

From novice to tech pro — start learning today.