Begin and End Date

christianbiker

Active Member
Joined
Feb 3, 2006
Messages
365
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top