Calculate a default date value based on todays date

[KA3]

I have a database which stores information relating to club members, which I'm in the middle of giving a bit of a facelift and one thing I want to automate is the calculation of the expiry date.

My memberships expire annually at the end of December and the expiry date is set as a default value within the table properties. This has to be manually changed every September (every new member from then pays for 15 months) to the following December.

Is there a formula I can insert in the defaul value field that will return December the current year if todays date is between January and August, and December next year if todays date is between September and December?

 

[Galaxiom]

= IIF(Month(Date())>8,DateSerial(Year(Date())+1,12,31),DateSerial(Year(Date()),12,31)

 

[KA3]

Thanks very much, I'll try that tomorrow night

 

[MarkK]

Cool solution! Marginally shortened . . .

=DateSerial(Year(Date())+IIf(Month(Date())>8,1,0),12,31)

 

[Galaxiom]

Just for variety and a little shorter still:

=DateSerial(Year(Date())-(Month(Date())>8),12,31)

The result of the comparison (Month(Date())>8) is implicitly cast to -1 for True or 0 for False.

 

[vbaInet]

Mark have you got anything shorter?

 

[MarkK]

Well, I guess there's . . .

="31-Dec-" & Year(Date())-(Month(Date())>8)

 

[vbaInet]

Not quite a date type though Mark. You've got to do better otherwise Galaxiom will beat you to this.

 

[Galaxiom]

Not quite a date type though Mark. You've got to do better otherwise Galaxiom will beat you to this.

It is a string but will cast to a date as the default in a date field.


Here is a winner by a character over Mark's

="31/12/" & Year(Date())-(Month(Date())>8)

 

[vbaInet]

It is a string but will cast to a date as the default in a date field.

I'm sure it will, but we want true date values, no shortcuts

Here is a winner by a character over Mark's

="31/12/" & Year(Date())-(Month(Date())>8)

Mark!!!

 

[Galaxiom]

Applying the datatype rule:
Before Mark gets back, two more characters saved:

=DateSerial(Year(Now())-(Month(Now())>8),12,31)

 

[vbaInet]

Clever cheat with Now().
I'm not sure how Mark is going to be able to recover from this one.

 

[MarkK]

Naw, I'm busted, outta bullets. Gotta give it to . . .

=DateSerial(Year(Now())-(Month(Now())>8),12,31)

Also, you guys are total nerds.

 

[vbaInet]

Naw, I'm busted, outta bullets. Gotta give it to . . .

This has got to be Round 1, so Galaxiom takes this one . I'm sure there will be other opportunities for you to redeem yourself (bring on Round 2!!!).

Also, you guys are total nerds.

Yeah you guys -->

...

...

 

[KA3]

Thanks folks, that's worked a treat