Jump to content

This topic is 7440 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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!

Posted

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?

Posted

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. wink.gif

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

Posted

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!

This topic is 7440 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.