Expiration Formula

[debbiep]

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))

 

[debbiep]

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]))

 

[Pieter Wijnen]

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>

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]))

 

[Pieter Wijnen]

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"

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>

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]))