Multiple rule date formula.

Question

Good afternoon,&nbsp;

I currently have a formula date named "Sanitary Survey Due" based on a different field:
WeekdayAdd([Last Hazard Survey:], 1305)
I also need this formula to have it so the date that is displayed for Sanitary Survey Due be ONLY the year, and not a date, but I don't know how to do this.&nbsp;------------------------------Newman, Savi------------------------------

miketamoush · Answer

I think there are many ways to accomplish this, it depends on the end goal. Perhaps you want a table report where entries are highlighted during the entire year they are due? Something like that?
A few ideas:
First, you may want to use the AdjustYear function, instead of adding days. So:
AdjustYear([Last Hazard Survey:],4) would return the original date plus 4 years. This helps with leap years, though maybe is moot.&nbsp;
Year(AdjustYear([Last Hazard Survey:],4)) would return just the year that the next survey is due.
Perhaps then you could have a checkbox: [Is Survey Due this Year?] = Year(Today())-Year(AdjustYear([Last Hazard Survey:],4))
or
Perhaps you just want to see if the next Survey is due? Below would check a box if it is the year of the due date or greater.
[Is Survey Due or Past Due] = Year(Today()) &gt;= Year(AdjustYear([Last Hazard Survey:],4))
I guess I am not sure your exact goal. Perhaps you could make a table report where one of those checkboxes is checked, and someone could always be looking at it and sending people out for Surveys?
------------------------------Mike Tamoush------------------------------

miketamoush · Answer

There is a year function in QB to accomplish just this.
[Year Of Sanitary Survey Due] = Year([Sanitary Survey Due])
I believe the formula will need to be formula numeric.------------------------------Mike Tamoush------------------------------

savinewman1 · Answer

That didn't work.
So they have a date function that returns the year.
I just can't figure out how to incorporate it into my current date formula.

Year&nbsp;(Date d)Description:&nbsp;Returns the year number of the Date d.Example:&nbsp;Year([Start Date]) returns the year of the date that appears in the Start Date field.Year(ToDate("Jan 10, 2000")) returns 2000

Now adding that information with my current "WeekdayAdd([Last Hazard Survey:], 1305)"

Is my problem. :( ------------------------------Newman, Savi------------------------------

miketamoush · Answer

Try:
Year(WeekdayAdd([Last Hazard Survey:], 1305))
Remember, your formula currently returns a date. So if you are only returning the year, your field needs to be a formula numeric, not a formula date.------------------------------Mike Tamoush------------------------------

savinewman1 · Answer

Hmm, maybe I don't understand. :/&nbsp;
So, here's the end goal.

We go out to our client on a specific date, 4 years later, we need to return to the client (no specific date, just sometime during the year 4 years later on initial visit) , then after the return 4 years later, we need to show back up to our client a year later (no specific date, just sometime during the year)&nbsp;

How on earth can I accomplish this?------------------------------Newman, Savi------------------------------

Forum Discussion

Multiple rule date formula.