How to store a dynamic date as a date variable

Hi

I'm trying to construct dates which I can store in variables as dates.  The dates themselves will be variable as they are based on different points in the academic year.

For this year, the dates will be (UK format):

31/12/2012
31/03/2013
31/07/2013

These will need to change in future years.  I've tried manipulating using DatePart and it has the right numbers in, but isn't in date format, e.g. 31/12/2012 comes out as 31122012.  Is there any way I can convert this to a date so that I can use the variable to compare against cell contents?

I'm awarding 500 points for a simple answer, but won't be able to look at it again until tomorrow morning.

Thanks
Sarah
ScuzzyJoAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
Sarah

If the dates are the same each year can't you just calculate them in the code?

Something like this perhaps:
dtSpring = DateSerial(Year(Date)-1,12,31) ' 31 Dec 2011
dtSummer = DateSerial(Year(Date),7,31) ' 31 Jul 2012
etc

Open in new window


I know the dates probably don't match the semesters but they are (some) of the dates you mentioned.
0
 
flaphead_comCommented:
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?
0
 
NorieVBA ExpertCommented:
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?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
redmondbCommented:
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#

Open 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.
0
 
flaphead_comCommented:
Depending on what you need to do, you could just use conditional formatting or filtering
0
 
ScuzzyJoAuthor Commented:
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
0
 
ScuzzyJoAuthor Commented:
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
0
 
flaphead_comCommented:
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
0
 
NorieVBA ExpertCommented:
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

Open in new window

0
 
ScuzzyJoAuthor Commented:
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
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.