Multiple rule date formula.
Good afternoon,
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 da...
Forum Discussion
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
------------------------------
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 (Date d) Description: Returns the year number of the Date d. Example: 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
------------------------------
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
------------------------------Hmm, maybe I don't understand. :/
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)
How on earth can I accomplish this?
------------------------------
Newman, Savi
------------------------------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.
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()) >= 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
------------------------------
