# Calculating date value based on a column value

I've been doing a lot of research and struggling to get the syntax down for "Column B." Any help is appreciated.

"Column A" is a manually entered date "ddmmyyyy." I want to calculate "Column B" automatically based on this date. If "Column A" is before March 1, 2016 the result in "Column B" should be [Column A]+100, if after March 1, 2016 the result should be [Column A]+50. The +100/50 represents days added to the date in "Column A."

Edit: Thanks for the responses. Column A is a date field set to date and time format, would that make a difference?

I input the formula as =IF([A]<1/3/2016,[A]+100,A+50) and the output is only providing the +50 regardless of date, unless Column A is blank in which case the result is +100. Any suggested changes appreciated.

## 4 Answers

Others are forgetting to convert 3/1/2016 to an actual date so it can compare to Column A. Here is your formula:

`=IF([Column A]<DATE(2016,3,1),[Column A]+100,[Column A]+50)`

The formula you are looking should be something along the lines of this:

``````=IF(A<1/3/2016,A+100,A+50)
``````

Set the column to return date.

SharePoint calculated columns are just excel formulas (with some restrictions) and as such if you search for excel formulas/make your own in excel you can usually port them over into SP with no problem.

• Thomas, note that this will work as long as Column A is a date field (not a string ddmmyyyy). – Erin L Mar 29 '16 at 13:03
• Column A is a date field set to date and time format, would that make a difference? I input the formula as =IF([A]<1/3/2016,[A]+100,A+50) and the output is only providing the +50 regardless of date, unless Column A is blank in which case the result is +100. – Thomas Mar 29 '16 at 14:27
• You can shorten the logic to `= A + IF( A<1/3/2016 , 100 , 50 )` If 1/3/2016 is misinterpreted because of locale setttings you can use the DATE function to ensure a correct Date: `DATE( year , month , day ) ` More at: viewmaster365.com/365coach/#/Calculated_Column_Functions_List – Danny '365CSI' Engelman Mar 29 '16 at 18:09

A calculated column is not the best way to do this; a workflow is. You want Column A and Column B to be Date/Time fields. Then write a workflow on create and edit that checks whether Column A is less than 3/1/2016. If so, add 100 days, else add 50 days (using Add Time to Date action). Then set Column B equal to the output variable. • Thanks! Unfortunately I don't have the ability to create custom workflows under our permissions so I was hoping to implement this with a calculated column. – Thomas Mar 28 '16 at 22:33
• Roland's formula will work – Erin L Mar 29 '16 at 13:03

Try this

``````IF([Column A]<1/3/2016, "[Column A]+100", "[Column A]+50")
``````
• This will not work with quotes around the date calculations – Erin L Mar 29 '16 at 13:02