0

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 4

1

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)

0
2

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.

3
  • Thomas, note that this will work as long as Column A is a date field (not a string ddmmyyyy).
    – Erin L
    Commented Mar 29, 2016 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
    Commented Mar 29, 2016 at 14:27
  • 2
    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 Commented Mar 29, 2016 at 18:09
0

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.

enter image description here

2
  • 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
    Commented Mar 28, 2016 at 22:33
  • Roland's formula will work
    – Erin L
    Commented Mar 29, 2016 at 13:03
0

Try this

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

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.