1

I am having trouble setting up my Column Validation formula on a list. I need to restrict the field to only be the 10th of any given month.

I found the below answer, but I don't want it to be the 10th day of the month excluding weekends, I need the date to be the 10th.

You'll have to build it in pieces - will take a bit of experimentation but

First build the date of the 10th.

Date(year, month, 10) - not sure what your date field you are building off off, but if you built it off of the created date it would look something like

Date(Year([Created]), Month([Created]), 10)

Then you'd have to test to see if that was a weekend, and subtract 1 day if it was a Saturday, or 2 if it was a sunday.

I haven't tested this, but something along these lines. weekday 6 is Saturday, weekday 0 is sunday

Where I have an X, replace that with Date(Year([Created]), Month([Created]), 10)

=IF(WEEKDAY(X) = 6, X-1, if(WEEKDAY(X) = 0, X-2,X ) ) https://social.technet.microsoft.com/Forums/office/en-US/c13b8150-2fff-4eb5-846b-888c392f89b8/set-deadline-date-as-10th-day-of-every-month-excluding-weekends?forum=sharepointgeneralprevious

I've been trying to imitate the formula, but am getting syntax errors:

=IF(Date([Effective Date])=Date(Year([Effective Date]),Month([Effective Date]),10))
Improve this question
2
  • 1
    If the 10th of any month is the only valid choice, then why give the user a date picker at all? Wouldn't a more generic choice column with months work?
    – Eric Alexander
    Commented Aug 29, 2016 at 17:29
  • It's been mentioned as an option (and would certainly be much easier), but the end users are in Finance and have to obey SOX requirements. The generic choice column may work if there's no other way, but it helps them out in audits if they can show the year that this was effective for without providing additional documentation.
    – rolemartyr-x
    Commented Aug 29, 2016 at 17:34

2 Answers 2

Reset to default
3

You can use below formula

=IF(Day(DateField)=10, true, false)
Improve this answer
2

It may be that you are overcomplicating your formula. This formula works to return a date set to the 10th of the month for the field/column date for a new field/column:

=DATE(YEAR([Date]),MONTH([Date]),10)

I'm assuming that you are happy with creating a calculated column which you then intend to use later. If you want to reset the value of the date column itself, you can do this with a workflow (there are some other options involving code).

What I have found helpful is to add a year field and a month field (and even a year-month field) and then sort/group/filter views with those. This would have the advantage that if the business workflow ever changed from the 10th to the 15th, filters based on the month would remain unchanged.

If you want to have a more complicated formula, for instance checking to see if the date field exists, you can certainly re-do the formula (can't edit it very easily once the fields have been added). When first starting out with SharePoint field formulas I like trying them our in Excel, as the error messages are better, and the formulas are very similar. Here is a link to Microsoft's examples of common SharePoint calculated field formulas: https://support.office.com/en-US/article/Examples-of-common-formulas-in-SharePoint-Lists-D81F5F21-2B4E-45CE-B170-BF7EBF6988B3

Improve this answer
1
  • I appreciate the answer. We were trying to do something a little different in that we already had the list and columns created, the users just asked if we could restrict the entries for this column. Looking forward, if something like this were to come up with a new project, I think I would lean toward doing it this way.
    – rolemartyr-x
    Commented Aug 31, 2016 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.