Year Number of Entry Based on Start Date

[skaterbase]

Date is date of current entry.

DateStart is a static date in the past.

How do I create a calculation that will show the Year Number of the Date based on DateStart?

For example:

DateStart = 1/1/00

If Date = 1/1/00...12/31/00, Year = 1

If Date = 1/1/01...12/31/01, Year = 2

If Date = 1/1/02...12/31/02, Year = 3

Thanks!

[transpower]

yn = Year(date) - Year(DateStart) + 1

[skaterbase]

Thank you, thank you, thank you!

[skaterbase]

Hmmm... This works as long as I'm using the date 1/1 but isn't working when I change DateStart to another date. I wasn't clear in my post before that DateStart isn't always a 1/1 date.

For example, here's what I need to see:

DateStart = 5/15/00

If Date = 5/15/00...5/14/01, Year = 1

If Date = 5/15/01...5/14/02, Year = 2

With the calculation as I tried it, Year is showing 2 as soon as Date crosses into 2001.

Any ideas?

[RalphL]

This is a modification of the age calculation.

See post by Queue

http://www.fmforums.com/threads/showflat...true#Post124141

[MoonShadow]

Is there the possibility that either field will ever be empty? As it stands, you will get a '?' in your Year field if either field is ever blank. If you wish to protect from this, you could wrap it with:

If(Date and DateStart, Year(Date) - Year(DateStart) + 1, "")

This forces a boolean test and it will only evaluate if both fields contain a date. I prefer to protect from this possibility on every date calculation.

Update: ahhh, I was wondering if you really wanted someone with DateStart 12/31/00 and Date 1/1/01 to reflect 2. But I used your 1/1...12/31 example...

oh, and I added ,"" on the end of the above If(). I forget that If() in versions prior to 7 require it; whereas in vs. 7, a null result is assumed.

With your new information you will want a date calc a bit more robust that either mine or Transpower's. In addition to Ralph's suggestion, I recommend that you also check out: Convert Age Calculation to Grade

[skaterbase]

The final formula:

Year(Date) - Year(DateStart) - (Date(Month(Date) , Day(Date), Year(DateStart)) < Date(Month(DateStart) , Day(DateStart) , Year(DateStart))) + 1

Thanks so much to all of you. I love this forum!