Anniversary Date Calculation

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Anniversary Date Calculation

Post by Leesha »

Hi,
I have a user who has rates that go up every year on the anniversary date that a company opens. There are all sorts of formulas that are calculated based on the year number. I thought I had the formula correct but unfortunately not. [start date] is the date that the txtAnnivDate is being calculated from. For example:

If [start date] = 1/26/2020 then and the calculation is being done today txtAnnivDate would have a 1 in the cell since its during the first year of operation.
If [start date] = 1/26/2020 then and the calculation is being done 3/1/201 txtAnnivDate would have a 2 in the cell since its during the second year of operation, and so on.

What would the formula look like?

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Anniversary Date Calculation

Post by HansV »

If txtAnnivDate is a column in a query, use

txtAnnivDate: (Year(Date())-Year([start date]))+(DateAdd("yyyy",Year(Date())-Year([start date]),[start date])>Date())+1

If txtAnnivDate is a text box on a form or report, set its Control Source to

=(Year(Date())-Year([start date]))+(DateAdd("yyyy",Year(Date())-Year([start date]),[start date])>Date())+1
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Anniversary Date Calculation

Post by Leesha »

Thanks Hans! It's perfect!
Leesha