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.

|improve this question
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)

|improve this answer
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.

|improve this answer
  • 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
  • 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 – Danny '365CSI' Engelman Mar 29 '16 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

|improve this answer
  • 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
0

Try this

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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