Hi All,

I just want to add a variable to my form but want to find out if there
is an easy way first.

Basically I want to define a variable based on the date in a date
picker, lets call it "Grant Year". Now the "Grant Years" run from
August to July,

a start date of 1st August 2005 would be in the 05/06 grant year, as
would be any start date after 1st August 2005 and before 31st July
2006, then 1st August 2006 would be 06/07 Grant Year and so on.

I dont want to declare the variable for every year, is there an easier
way to declare it based on day/month/year so that I dont have to cover
all years manually?

(I.e i dont want to have to do this below)

if dtpicker1.value = "01/08/2005" and dtpicker1.value <= "31/07/06"
then
Variable1 = "05/06 Grant Year"
end if

if dtpicker1.value = "01/08/2006" and dtpicker1.value <= "31/07/07"
then
Variable1 = "06/07 Grant Year"
end if

and so on and so on until infinity

Hope this is possible, if not I will have to do it the long way!

Many thanks

Duncan

if month(dtpicker.value)=8 then
variable1 = left(year(dtpicker.value),2) & "/" & _
left(year(dtpicker.value)+1,2) & " Grant Year"
else
variable1 = left(year(dtpicker.value)-1,2) & "/" & _
left(year(dtpicker.value),2) & " Grant Year"
endif


Co

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=56325

Col,

Many thanks for your reply,

I changed the 'Left' to 'Right' as for some reason (I couldnt really
get to grips with why) if was giving me "20/20 Grant Year", after I
changed to 'Right' if worked perfectly

I think it is probably how I have defined my variable but Im not
changing it now because it works!

Many thanks again

Duncan


colofnature wrote:

if month(dtpicker.value)=8 then
variable1 = left(year(dtpicker.value),2) & "/" & _
left(year(dtpicker.value)+1,2) & " Grant Year"
else
variable1 = left(year(dtpicker.value)-1,2) & "/" & _
left(year(dtpicker.value),2) & " Grant Year"
endif


Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=563259

if month(dtpicker.value)=8 then
variable1 = right(year(dtpicker.value),2) & "/" & _
right(year(dtpicker.value)+1,2) & " Grant Year"
else
variable1 = right(year(dtpicker.value)-1,2) & "/" & _
right(year(dtpicker.value),2) & " Grant Year"
endi

--
MattShoreso
-----------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...nfo&userid=347
View this thread: http://www.excelforum.com/showthread.php?threadid=56325

Oops, yeah, sorry about that... One of these days I'll learn to tell
left from right, and then I'll be invincible.


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=563259