Using two formulas in one calculated column

Question

_{Monday, February 23, 2015 10:27 AM}

Hi, I'm working on a List within SharePoint 2010 that uses dates. I am trying to work out if a field is blank then it performs the one formula and if the field is populated then it works out another formula.

To give you an example:

Field A = Today's Date (WorkReceived)

Field B = Field A + 7 Days (working out a week from the work is received) - (CompleteBy)

Field C = Work Completion Date

Field D = Progress

Most of the calculations would happen in Field D, I am trying to work out a formula that would calculate 2 formulas. So if Field A and Field B are populated then the formula would be Field A - Field B and Field C would be blank. If Field C is populated then the formula would be Field B - Field C. So Field D would then work out the difference in days.

I've currently worked out a formula to calculate Field A - Field B and Field D would display the number of days between the both dates(excluding Weekends) which the formula I have at the moment is:

=DATEDIF(CompleteBy,WorkReceived,"d")

But I seem to run into a few problems when i try to add another calculation to the formula if Field C is populated and it would skip the calculation of Field A.

Would anyone know if this can be possible within a calculated column ?

Any help would be great or any other advice would be great thanks.

James

All replies (4)

_{Monday, February 23, 2015 10:42 AM}

Hi James,

You can use conditional formulas for populating your fields.

https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx#sectionSection1

Best Regards,
Brij K
http://bloggerbrij.blogspot.co.uk/

_{Monday, February 23, 2015 10:48 AM}

Hi Brij,

Thanks for you reply, I have had a look into the CONCATENATE function to perform a formula if a field is blank, do you think that would be beneficial?

regards

James

_{Monday, February 23, 2015 11:01 AM | 2 votes}

Hi,

You can use the function IsBLANK to check whether the field has value or not.

=IF(ISBLANK([WorkCompletionDate]),DATEDIF([WorkReceived],[CompletedBy],"D"),DATEDIF([CompletedBy],[WorkCompletionDate],"D"))

And also refer to the following article to avoid weekends

http://blog.pentalogic.net/2008/11/working-days-weekdays-holidays-sharepoint-calculated-columns/

Please don't forget to mark it as answered, if your problem resolved or helpful.

_{Monday, February 23, 2015 12:07 PM}

Hi Mokhtar,

Thanks for your help, it has worked, I started using the ISBLANK statement before but my syntax was wrong so thanks for the help, although it returns some in date formats such as 06/01/1900 if there is nothing in CompleteBy field. So I don't know why it is returning a date when I'm

needing days?

Kind Regards

James