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
Anniversary Date Calculation
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Anniversary Date Calculation
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
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
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Anniversary Date Calculation
Thanks Hans! It's perfect!
Leesha
Leesha