When creating custom formula fields, an Excel formula that is tested within Excel may not be valid in the software. Check the following items to ensure the formula is valid for use:
- Formula must only use commas.
- You used the Insert Wildcard process and did not manually enter the wildcards into the formula.
- If unsure of how the content is displayed for a specific field, start with some equal formulas. For example, to create a formula based on an event status, check for "={Event Status}" and the field provides you with a "Confirmed (30)".
Below is an example of a formula which checks for event status, sensitivity and the release date. An event with an event status of 30, a Public sensitivity and the release date in the past displays as "Public Announcement". All other events display with "Not Announced".
=IF({Status}="Confirmed (30)", IF({Sensitivity}="Public", IF({Release}<TODAY(), "Public Announcement", "Not Announced"), "Not Announced"), "Not Announced")
Comments
4 comments
How can I create an "IF" formula to cause a date to populate only on a WEEKDAY instead of a weekend?
Example: =IF(WEEKDAY(In Date,1))=(Sat or Sunday) = the next business day. --not sure what to use here and I want it to land on a weekday instead of a weekend. I would use the WORKDAY option but your system doesn't support it.
Thanks
0 upvotes
Hi,
If all Saturday's and Sunday's would become Monday, then you could use this formula.
=If(OR({In Day of Week}="Saturday",{In Day of Week}="Sunday"),"Monday",{In Day of Week})
You would then show the formula field on the event screen to show you the In Business day of the week.
0 upvotes
Thanks David, I realized I missed a detail in my 1st question. I need the new date to be (In Date) -31 and then land on a week day. I tried a few different ways to add your formula and it comes up invalid. Do you know how to make the "In Date" start 31 days after and land on the Monday?
I appreciate any help.
0 upvotes
Hi Nina Dennison,
I have setup a couple of formula to hopefully get the result you want. If the In date + 31 days is a Saturday, it will add 2 days to it to make it the Monday and 1 day if it's a Sunday.
The first is the In date + 31 days
The second is the day of the week the In date + 31 days is.
Then the last one adds the days if it's a Saturday or Sunday to make it a Monday.
0 upvotes
Please sign in to leave a comment.