Expiration Formula

One more question. This formula works great, with the exception of, if
someone signed the paperwork prior to 9/2, it tells me their paperwork
expired 9/1/07. I need it to tell me that if Kool Kidz signed before
9/2 that it will expire one year later; otherwise if signed on 9/2 and
later it will expire on 9/1 of the next year.

It works great if the [Date] is 9/2/07 and greater.
Now my situation is if the [Date], for example is 8/31/07, it shows
an expiration date of 9/1/07. If the [Date] is less than 9/2/07, it
needs to be 1 year from [Date]. Is this possible. Thanks again.

This is what I have so far.

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kool Kidz",DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1))

On Sep 30, 10:49 am, "
wrote:
One more question. This formula works great, with the exception of, if
someone signed the paperwork prior to 9/2, it tells me their paperwork
expired 9/1/07. I need it to tell me that if Kool Kidz signed before
9/2 that it will expire one year later; otherwise if signed on 9/2 and
later it will expire on 9/1 of the next year.

It works great if the [Date] is 9/2/07 and greater.
Now my situation is if the [Date], for example is 8/31/07, it shows
an expiration date of 9/1/07. If the [Date] is less than 9/2/07, it
needs to be 1 year from [Date]. Is this possible. Thanks again.

This is what I have so far.

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kool Kidz",DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1))

YEAH. I got it. Thank you for all your suggestions. This is the final
formula.

=IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1),DateAdd("yyyy",1,[Date]))

Ammend the [ClassroomID]="Kool Kidz" to
[ClassroomID]="Kool Kidz",IIF(CLng(Format([Date],"mmdd"))902),
DateAdd("yyyy",1,[Date]),DateSerial(Year([Date]), 9,1))

HTH

Pieter

BTW The reserved words thingy isn't all that bad, you just have to remember
to always use hard brackets [] g

wrote in message
oups.com...
On Sep 30, 10:49 am, "
wrote:
One more question. This formula works great, with the exception of, if
someone signed the paperwork prior to 9/2, it tells me their paperwork
expired 9/1/07. I need it to tell me that if Kool Kidz signed before
9/2 that it will expire one year later; otherwise if signed on 9/2 and
later it will expire on 9/1 of the next year.

It works great if the [Date] is 9/2/07 and greater.
Now my situation is if the [Date], for example is 8/31/07, it shows
an expiration date of 9/1/07. If the [Date] is less than 9/2/07, it
needs to be 1 year from [Date]. Is this possible. Thanks again.

This is what I have so far.

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kool Kidz",DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1))

YEAH. I got it. Thank you for all your suggestions. This is the final
formula.

=IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1),DateAdd("yyyy",1,[Date]))

That should've been

[ClassroomID]="Kool Kidz",IIF(CLng(Format([Date],"mmdd"))902),
DateAdd("yyyy",1,[Date]),DateSerial(Year([Date])+1, 9,1))

And The whole thing can be written as

IIf ([ClassroomID]="Kool Kidz" And CLng(Format([Date],"mmdd"))901,
DateSerial(Year([Date])+1, 9,1), DateAdd("yyyy",1,[Date]))

HtH

Pieter



"Pieter Wijnen"
it.isi.llegal.to.send.unsollicited.mail.wijnen.no
wrote in message ...
Ammend the [ClassroomID]="Kool Kidz" to
[ClassroomID]="Kool Kidz",IIF(CLng(Format([Date],"mmdd"))902),
DateAdd("yyyy",1,[Date]),DateSerial(Year([Date]), 9,1))

HTH

Pieter

BTW The reserved words thingy isn't all that bad, you just have to
remember to always use hard brackets [] g

wrote in message
oups.com...
On Sep 30, 10:49 am, "
wrote:
One more question. This formula works great, with the exception of, if
someone signed the paperwork prior to 9/2, it tells me their paperwork
expired 9/1/07. I need it to tell me that if Kool Kidz signed before
9/2 that it will expire one year later; otherwise if signed on 9/2 and
later it will expire on 9/1 of the next year.

It works great if the [Date] is 9/2/07 and greater.
Now my situation is if the [Date], for example is 8/31/07, it shows
an expiration date of 9/1/07. If the [Date] is less than 9/2/07, it
needs to be 1 year from [Date]. Is this possible. Thanks again.

This is what I have so far.

=Switch([ClassroomID]="Bumble Bears",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Doodlebugs",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Dandeelions",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kinder-Ready",DateAdd("yyyy",1,[Date]),
[ClassroomID]="Kool Kidz",DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1))

YEAH. I got it. Thank you for all your suggestions. This is the final
formula.

=IIf([ClassroomID]="Kool Kidz" And
(Format([Date],"mmdd")901),DateSerial(Year([Date])-
(CLng(Format([Date],"mmdd"))901),9,1),DateAdd("yyyy",1,[Date]))