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.

7 Replies

Related Content

Multiple reference proxy fields help

Managing record access for multiple data levels

Formula URL - Edit then Add

Dynamic Form Rules

Multiple form rules set for a group of sections, but only the last Rule fires

Recent Discussions

Bug with Advanced Query in Pipelines Designer?

Date value changes when copied to new table with pipeline.

Share your feedback on the new Qrew Discussions

'Is Set' in Pipelines

Time of Day field values are not in 24-hour format in Pipelines