# Calculate DATE with based on column + IF statement

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)
)
``````

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;

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

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...

• Thanks, What do I use instead of YEAR? support.office.com/en-us/article/… says there is only one output 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. Feb 20, 2017 at 20:51
• [Tested:] will always be a valid date. Feb 20, 2017 at 20:51

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))))

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