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
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
------------------------------sorry to jump in here, but what i'd proably look at splitting this up into a few datafields
--- Next_visit_year (the year stored as a number using the formula provided above)
--- Next_scheduled_visit (date,assuming you set schedule manually at somepoint)--- Actual_date_of_visit ( date, recorded when you visit the site )
You can use pipelines if you want to push to a team calendar, generate metrics for alignment with schedule, get lists of sites to visit by year of next visit, etc.Field staff update the Actual_date after their visit. Keep the scheduled date editable unless the site has been visited, when the "Actual_date" is updated create the next_visit_year. Use piplines to push/edit linked calendars, etc.
You can get a number of useful metrics
------------------------------
joe vandervest
------------------------------Thanks to you both!
------------------------------
Newman, Savi
------------------------------
