How to store a dynamic date as a date variable

Not sure I follow you.  How do you want to compare the dates?

So if you had 31/12/2012 in Cell B2 and in Column D you have a whole range of dates, do you want to see which cells in column D are greater than 31/12/2012?

Sarah

How did you try to manipulate them?

Why do you need to manipulate them?

You shouldn't need to do that to compare them to other dates.

PS How do the dates change every year?

Hi, Sarah.

There are a number of possibilities from your question, but following your request for simplicity...

Dim DATE1 As Date
Dim DATE2 As Date
Dim DATE3 As Date
DATE1 =  #31/12/2012#
DATE2 =  #31/03/2013#
DATE3 =  #31/07/2013#

Select allOpen in new window

You can then directly compare the three variables to cells containing standard Excel dates, e.g.
If ActiveCell = DATE1 then etc. etc.

Regards,
Brian.

Depending on what you need to do, you could just use conditional formatting or filtering

Hi All

Thanks for all your answers.  I think I need to explain it a bit more.

Firstly, the date doesn't come from a cell.  It's in the VBA itself.  It's all to do with university admissions and fee payments.  What I'm trying to do is to say, e.g. if today's date is 30/11/2102 and if the date in a particular cell is before 31/12/2012, then mark the row as "relevant" because today's date is in the autumn term.  Another example would be, if today's date is 28/02/2013 and the dates in two cells are 31/12/2012 and 05/01/2013, then mark both rows as relevant because they both started before the end of the spring term.

It comes from the fact that we have multiple course start dates throughout the year and our academic year doesn't fit with the standard academic year.  I'm a consultant for a distance learning university which has courses and modules starting throughout the year, unlike the traditional universities.  Students can register for modules at any time during the year and there are already quite a few who have registered for a spring module at the same time they registered for the autumn one.  For my purposes, the student becomes liable at the beginning of the relevant term.

In the autumn term, I will want to mark all the records (rows) which have course start dates before the end of the term as relevant.  In the spring term, I want to mark all the records which have module start dates before the end of the autumn term and all those which have start dates before the end of the spring term as relevant, and so on.  Obvioulsy, the term end dates will change, not only from term to term, but from year to year.  They will always be the last day of December, March, July and August, but the years will change.

I then have a For....Next loop which compares the dates in the actual cells to these criteria and marks the rows appropriately via a Case statement.  There will be thousands or rows each time to compare, so this seems to be the quickest way of doing it.

Hope that clarifies it a bit.

Thanks
Sarah

Hi All

I think I've worked it out!  I've got the following, which I've just run through with F8 and it seems to be storing as a string and then converting it to a date, though I haven't tested the whole macro yet:

termOne = DatePart("m", #12/31/2012#) & "/" & DatePart("d", #12/31/2012#) & "/" &
    DatePart("yyyy", Now)
termOne = DateValue(termOne)

termTwo = DatePart("m", #3/31/2012#) & "/" & DatePart("d", #3/31/2012#) & "/" & DatePart
    ("yyyy", Now + 365)
termTwo = DateValue(termTwo)

termThree = DatePart("m", #7/31/2012#) & "/" & DatePart("d", #7/31/2012#) & "/" &
    DatePart("yyyy", Now + 365)
termThree = DateValue(termThree)

I'll try it out and see if it works when I've written the rest of it.

Thanks
Sarah

instead of VB you could just use conditional formatting

Say in A1 you put 30/12/2012

Then in
A2 = 01/11/2012
A3 = 01/01/2013
etc etc
select A2 & A3
Click on Condition formatting
Format only cells that contain
Cell value less than $a$1
Select the fill etc you want ..
Done

Now if you change the value of A1 the formatting with adjust

Sarah

That's similar to my idea, but with the code I posted you wouldn't need to convert to a date.

' DateSerial(YY, MM, DD) returns a date

termOne= DateSerial(Year(Date(), 12, 31) ' 12/31/2012

termTwo= DateSerialYear(Date())+1, 3, 31) ' 03/31/2013

termThree= DateSerial(Year(Date())+1, 7, 31) ' 07/31/2013

Select allOpen in new window

Hi

I've tested the whole thing and both my version Imnorie's DateSerial work, so I'm awarding the points to Imnorie.

Thanks to everyone for your help.

Sarah