Calc Field snafu for an age range, in days.

[niktemadur]

Hey everybody. Can anybody help with a tricky situation here?

I have a Date Field for Birth Date. Then I have 3 Calc Fields to determine a person's Age: one is Years, one is Months and the last one is Days.

So far, so good. Now here's the tricky part.

I want to make a Layout in List View that shows the people whose birthday it is today, those whose birthday is coming up, as well as those whose birthday has just passed, in case I didn't check my database for a couple of days. This way, running a Search Script will make it a cinch to keep track of people's birthdays.

I want to make a Calc Field with two results, 1 (one) and 0 (zero). Result =1 (one) is for those people who fit a range: 2 days before to 2 days after their birthday. Result = 0 (zero) is for everybody else.

The problem: I can't figure out how to make the birthday minus 2 days part working. The top of the range, zero months plus two days, is easy, but since months have irregular durations, the bottom of the range varies depending on the month; I can't use eleven months plus "x" amount of days.

The question: How do I write a calculation that changes a person's status from 0 (zero) to 1 (one) when his or her birthday is 2 days away, and changes their status back to zero when their birthday is 2 days past?

Thank you for kind attention. This forum is spectacularly good.

[BobWeaver]

1-Abs(Sign(Int((Status(CurrentDate)-

Date(Month(Birthdate),Day(Birthdate),Year(Status(CurrentDate)))

)/2)))

This will give a value of one when the current date is within one day (+/-) of the birthday and zero for all other dates. If you want to expand the range to 2 days, change the 2 at the end of the calculation to a 3 etc.

Note - Birthdate is a regular date field containing the person's actual date of birth.

[BobWeaver]

Sorry, the formula I gave above, won't work properly when the birthday is at the beginning or end of the year. Use the following instead:

not Int((182.5-Abs(Abs((Status (CurrentDate)-

Date(Month(Birthdate),Day(Birthdate),Year(Status (CurrentDate))) ))-182.5))/2)

This will actually give you one extra day of leeway on leapyears if the birthday is on Dec 31 or Jan 1, but I doubt if that will create any problems for you.

[Robert Kidd]

try:-

Case(Birthdate <= (Status(CurrentDate)+2) and Birthdate >= (Status(CurrentDate)-2),1,0)

[CobaltSky]

Hello Robert,

I'm afraid that the formula you're suggesting will only work for folks who are actually born in the current year. Anyone who happens to have been born more than two days ago is not going to show up unless you include a method for transposing earlier year birthdates into the current year - and when you do, you will get into the complexities that Bob was wrestling with in relation to birthdays close to new year.

While I like the thinking behind Bob's elegant constructs, both are imperfect - even if the second exhibits a 'safe' form of inconsistency (ie extra warning during leap years). I guess that for the sake of consistency, Bob, I'd be inclined to go for the rather more pedestrian:

Abs(Status(CurrentDate) - Date(Month(birthdate), Day(birthdate), Year(Status(CurrentDate)) + 1)) < 3 or

Abs(Status(CurrentDate) - Date(Month(birthdate), Day(birthdate), Year(Status(CurrentDate)) - 1)) < 3 or

Abs(Status(CurrentDate) - Date(Month(birthdate), Day(birthdate), Year(Status(CurrentDate)))) < 3

- which will return a 1 when a person's birthday is within two days (in either direction) of the current date - including at the start and end of the year and also during leap years.

BTW niktemadur, all these formulae would have to be placed in unstored calculations in order to ensure that they automatically update as the date changes.

[BobWeaver]

Hi Ray,

I agree it's best to have consistent results. I was trying to make my formula work precisely for all dates, but it was getting late and my brain was starting to conk out.

[niktemadur]

Wow. Thanks for all of your interest! CobaltSky, your formula seems to work perfectly so far. Interesting, subtracting the birthday from the year so far. The approach I thought had to be taken was completely off mark. I'll be chewing over it mentally to understand the logic and syntax within. Gotta brush up on those Status functions. Also, I'll be keeping daily tabs on it for a couple of weeks AND in the upcoming Leap Year.

Please let me take this formula question one step further. How can the following be done?

In another Calc Field, assign a value, depending on the distance to the birthday, like so:

Day Before Yesterday - 5

Yesterday - 4

Today - 3

Tomorrow - 2

Day After Tomorrow - 1

Again, 5, 4 and 3 are not so difficult, but 2 and 1 are the tricky ones.

[niktemadur]

OK. Plagiarizing a bit from Mr. Cobalt, I'm thinking along these lines for a Calc Field that assigns a different value for each day.

If(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate))) = -2, 5,

If(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate))) = -1, 4,

If(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate))) = 0, 3,

If(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate))) = 1, 2,

If(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate))) = 2, 1, 0)))))

How does it hold up to scrutiny?

[CobaltSky]

Hi niktemadur,

Your edited version as above, (ie with the Abs( ) removed) will return the find of results you're looking for when the birthdate and the current date are within the same year. However it will break over new year, like Bob's earlier suggestion.

I suggest that you use:

Choose(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate))) + 2, 5, 4, 3, 2, 1) +

Choose(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate)) - 1) + 2, 5, 4, 3, 2, 1) +

Choose(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate)) + 1) + 2, 5, 4, 3, 2, 1)

- which is a little more compact and will return consistent results throughout the year and also immediately before and after new year.

[niktemadur]

Yes! I see what you mean, CobaltSky.

Frustratingly, I am still unable to detect these things unless someone points them out to me. Like that old Connect Four game: "Here, diagonally".

BTW, for the formula to take the value 0 (zero), representing those who fall outside the date range, into account, I tweaked it a bit again to make it like so:

If(Previous Calc Field = 1, Choose(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate))) + 2, 5, 4, 3, 2, 1) +

Choose(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate)) - 1) + 2, 5, 4, 3, 2, 1) +

Choose(Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate)) + 1) + 2, 5, 4, 3, 2, 1), 0)

Thank you.

[Ugo DI LUCA]

Depending on the number of records you're holding, these added unstored calculations could become problematical.

Here's another way of doing what you're after.

1. Create an indexed calculation using your previous calcs for Days and Months

c_checkBirthday = Month&"-"&Day

2. Create a global field g_checkBirthday

3. A relationship 'BirthdayCheck' using the g_checkBirthday at Left side and the c_checkBirthday for the Right side.

4. A script fills the global with a range from Today-2 to Today+2, and switch to a List layout showing these Birthdays

SetField[g_checkBirthday,

Month(Status(CurrentDate)-2)& "-"&Day(Status(CurrentDate)-2)&"

[CobaltSky]

Hi niktemadur,

FWIW, it's possible to make the calc always return a zero for all values outside the date range, without making any change to the formula. It can be done by turning off the option labelled 'Do not evaluate if all referenced fields are empty' which appears at the lower left of the calculation dialog - and making sure that your calculation is set to return a result type of number.

There are several advantages of doing it that way - apart from the fact that it's easier. For instance, because both calcs are unstored, referencing the first within the second means that the first must be recalculated every time the second is evaluated, which doubles CPU overheads.

Another quite separate consideration in relation to having the calc return a zero for all cases other than when the birthdate is within range is that it could be misleading in cases where the birthdate field is left blank. Ie it will still return a result (a zero) even when there is no data to report on. This would be the case with the formula you cited above, and also with the method I suggested here.

Ideally it would be best if the field returned a zero when the birthdate exists but is out of range, but remained blank or posted an alert if the Birthday field had no value. A way to force the calculation to do that - without referencing the pervious calculation would be to use something along the lines of:

Case(not IsEmpty(Birthday),

Choose(Min(3, Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate)))) + 2, 5, 4, 3, 2, 1, 0) +

Choose(Min(3, Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate)) - 1)) + 2, 5, 4, 3, 2, 1, 0) +

Choose(Min(3, Status(CurrentDate) - Date(Month(Birthday), Day(Birthday), Year(Status(CurrentDate)) + 1)) + 2, 5, 4, 3, 2, 1, 0)

)

- or, with the 'Do not evaluate if all referenced fields are empty' option deselected, you could use the above without the added Min( ) functions, to achieve the same effect.

In either case it will be more efficient than the formula you're currently considering because it does not reference any other unstored calcs, plus it will return seven states rather than six (viz: null, 0, 1, 2, 3, 4 and 5).

[Ugo DI LUCA]

In extension to your "Mark record" solution (the 1,2,3,4,5 results), if this document is for view only, you could create 5 global fields g_checkBirthday1-5 and draw 5 portals on a layout, each global field being "targetting" the c_checkBirthday field at Right side.

Then, use a script

SetField[g_checkBirthday1,

Month(Status(CurrentDate)-2)& "-"&Day(Status(CurrentDate)-2)]

SetField[g_checkBirthday2,

Month(Status(CurrentDate)-1)& "-"&Day(Status(CurrentDate)-1)]

SetField[g_checkBirthday3,

Month(Status(CurrentDate))& "-"&Day(Status(CurrentDate))]

SetField[g_checkBirthday4,

Month(Status(CurrentDate)+1)& "-"&Day(Status(CurrentDate)+1)]

SetField[g_checkBirthday5,

Month(Status(CurrentDate)+2)& "-"&Day(Status(CurrentDate)+2)]

GoToLayout[birthdayPortals]

Put each global field on the header of portal and this would give you a view of all birthdays, again without involving unstored calcs...

HTH.