2

my calculated date/time function is failing on sharepoint 2010 and I'm not sure why.

=YEAR( [Tested:] 
       +IF( [Maintenance Recurrence:]="Yearly"
            ,1
            ,0
          )
     )

Will work. But

=DATE( 
      YEAR( [Tested:]+IF( [Maintenance Recurrence:]="Yearly",1,0)
          )
     )

and more specifically the one I want.

=DATE(
    YEAR([Tested:]+IF([Maintenance Recurrence:]="Yearly",1,0))
   ,MONTH([Tested:]+IF([Maintenance Recurrence:]="Monthly",1,0)+IF[Maintenance Recurrence:]="Bi-Annually",6,0))
   ,DAY([Tested:]+IF([Maintenance Recurrence:]="Weekly",7,0)+IF([Maintenance Recurrence:]="Daily",1,0)))

Won't. I'd also like to know if there is an easier way to do this. I couldn't find a function to use to map text to #'s

Follow up question: I've read that trying to fight sharepoint's concept of how it works is a losing battle... So what's the "sharepoint" way/equivalent of iterating a workflow over all items in a list on a schedule. Because the only solutions I found to do this were EXTREMELY convoluted. (creating two lists with 3 workflows to simulate a foreach loop then putting a powershell script on a task schedule to run the workflow on a recurring basis......)

SOLUTION

Thanks Danny,

=DATE(
YEAR([Tested:])
+IF([Maintenance Recurrence:]="Yearly",1,0)

,MONTH([Tested:])
+IF([Maintenance Recurrence:]="Monthly",1,0)
+IF([Maintenance Recurrence:]="Bi-Annually",6,0)

,DAY([Tested:])
+IF([Maintenance Recurrence:]="Weekly",7,0)
+IF([Maintenance Recurrence:]="Daily",1,0)
)

2 Answers 2

1

DATE function wants 3 parameters, so YEAR only doesn't work;

Your last Formula is lacking several ( after the IFs

Best practice is to create your Formula in Notepad or another decent text-editor;
add loads of linebreaks and spaces/tab so you can read the Formula

That way you will immediatly spot your ( errors

When you copy/paste into a SharePoint Formula, SP will remove all linebreaks and spaces (so keep that copy in Notepad)

Function reference: http://www.viewmaster365.com/365coach/#/Calculated_Column_Functions_List

RE: comments

You are doing

 =YEAR(
       [Datefield] + 1
      )

I presume you mean to do:

 =YEAR(
       [Datefield]
      ) + 1

Since Dates in SharePoint start counting at december 31st 1899, your version (for YEAR) works for every day but the last day of the year.

But this (adding +1 inside the function) goes horribly wrong when you use the MONTH and DAY function

MONTH+1 works for every day BUT the last day of the Month

DAY+1 is wrong for every Day...

6
  • Thanks, What do I use instead of YEAR? support.office.com/en-us/article/… says there is only one output
    – Guillaume
    Feb 20, 2017 at 20:46
  • The DATE function requires Year,Month,Day parameters, a Date without a month or day doesn't make sense Feb 20, 2017 at 20:47
  • The YEAR Function wants a valid Date as parameter Feb 20, 2017 at 20:50
  • The YEAR function returns the year. Which I put in the YEAR parameter of the date function. Am I missing something? I changed the editing in my original command, should be more clear.
    – Guillaume
    Feb 20, 2017 at 20:51
  • [Tested:] will always be a valid date.
    – Guillaume
    Feb 20, 2017 at 20:51
1

On your last one you've got some parentheses in the wrong place. Especially when you've got 2 possibilities in the Month and Day sections. Try this:

=DATE(YEAR([Tested:]+IF([Maintenance Recurrence:]="Yearly",1,0)) ,MONTH([Tested:]+IF([Maintenance Recurrence:]="Monthly",1,IF([Maintenance Recurrence:]="Bi-Annually",6,0))) ,DAY([Tested:]+IF([Maintenance Recurrence:]="Weekly",7,IF([Maintenance Recurrence:]="Daily",1,0))))

1
  • Thanks, fixed in my original comment. But still says formula contains a syntax error or is not supported when I try to save.
    – Guillaume
    Feb 20, 2017 at 20:47

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.