Begin and End Date

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
Is it possible, using an If statement in VBA to set a range for 2 separate dates? For example, if the date falls within a range that I specify, can I set the code up to execute a task I determine?

I figured I could use something like below, but as soon as I try to go to the next line I get an error.

Any help would be appreciated.

Thanks.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
I am not sure if I explained what I wanted to do properly as I have not received a response as of yet. What I want to do is set up a range that has a beginning date and an end date. If the file falls within that range I want VBA to execute a task.

What I am doing is creating a reference number for various issues within a spreadsheet. I want to have the reference number include a specific number if it falls within the range I have specified when the issue is logged.

Does anyone have an idea on how to do this?

Thanks
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

Can you adapt this example. A1 contains start date say 1/1/06 and B1 contains end date say 31/3/06 C1 contains date in question say 28/2/06

run this macro from a module and if the date in question falls between the start date and end date the cell colour will turn red.

Code:
Public Sub date_test()
Dim startdate As Date, enddate As Date, mydate As Date
Worksheets("Sheet1").Activate
startdate = Cells(1, 1).Value
enddate = Cells(1, 2).Value
mydate = Cells(1, 3).Value
If mydate > startdate Then
    If mydate < enddate Then
        Cells(1, 3).Select
        Selection.Interior.ColorIndex = 3
    End If
End If
End Sub
 

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
I am not sure if I explained what I wanted to do properly as I have not received a response as of yet. What I want to do is set up a range that has a beginning date and an end date. If the file falls within that range I want VBA to execute a task.

What I am doing is creating a reference number for various issues within a spreadsheet. I want to have the reference number include a specific number if it falls within the range I have specified when the issue is logged.

Does anyone have an idea on how to do this?

Thanks

Although another poster posted what probably is a better way of doing this, I use a method that probably would work for another purspose. In my case I compare two dates (and times) and use datediff to calculate the number of hours between two dates and if greater then a specified amount a cells gets a particular value in it.

So if you take the two dates in your example, the start and ending date and compare the difference between those, then did the same with the start and the date you are testing, then the number of days should be less.

Code:
CreatedDate = ws.Cells(TransferRowNumber, 52)

ExpectedDate = ws.Cells(TransferRowNumber, 19)

ExpectedTime = ws.Cells(TransferRowNumber, 20)

ExpectedTime = TimeSerial(Left(ExpectedTime, 2), Right(ExpectedTime, 2), 0)

ExpectedDate = ExpectedDate + ExpectedTime

PlannedWarning = DateDiff("h", CreatedDate, ExpectedDate)

If PlannedWarning > 16 Then

Worksheets(wsDaily).Range("c" & perNum).Value = "y"

End If

In my example the date is in the format of mm/dd/yyyy. The time is in the format of hhmm (military time).

The two are added together after conversion of the time format then compared to the created date.

You would just need to add the test for hours different (or days, etc.) for the date between the two.

Perry

Perry
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
here is the starting line:

If Date >= #1/1/2006# and <= #12/31/2006# then

Try this syntax:
Code:
If Date >= #1/1/2006# and Date <= #12/31/2006# then
 

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
360
Actually that last code work perfect! I was off by just a bit. I figured there was a simple solution to the question.

Thanks alot Dean!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,415
Messages
5,547,807
Members
410,813
Latest member
Vhinzvirgo
Top