1

First, I have very minimal formula writing skills (I'm trying to learn). I was able to create a SharePoint list and have a calculated column titled [Shipment Date Summary] with the following formula:

="On ”&[Date Shipment Changed]&" Shipment date changed from ”&[Prior Shipment Date]&" to ”&[New Shipment Date]

  1. This formula works. However, if the columns are blank, my formatted text displays. How do I write a formula that returns a blank IF the fields I want to concatenate are blank ELSE maintain the formula operating as it currently is?

  2. Furthermore, I will create a [Date Summary] column which will consolidate the [Shipment Date Summary] data (from formula above) as well as 2 other calculated formulas for [Order Date Summary] and [Arrival Date Summary].

Improve this question

2 Answers 2

Reset to default
0

To check for blank you can use the ISBLANK or LEN function

Check this post

I did not get your second question.

Improve this answer
1
  • Thank you, Manu. Amal responded with a formula. About the second question, I need to create an overall [Date Summary] calculated column that consolidates data from various calculated columns (but yet, leaves this new column blank if no data is in any column)
    – Pspet
    Commented Jan 15, 2015 at 16:05
0

You can do the BLANK check using

IF(ISBLANK([FieldName], "empty", "not empty"))

So your final formula will be come something like 

=IF(ISBLANK([Date Shipment Changed]),"",IF(ISBLANK([Prior Shipment Date]),"",IF(ISBLANK([New Shipment Date]),"","On "&[Date Shipment Changed]&" Shipment date changed from "&[Prior Shipment Date]&" to "&[New Shipment Date])))

You can use the same approach for [Date Summary] field.

Improve this answer
7
  • Thank you, Amal. I tried using that formula and it did not work??? I get message "formula contains syntax error or is not supported". I checked the formula you wrote and edited the spacing to see if that was issue, but i could not get it to work? I am pretty new at this.
    – Pspet
    Commented Jan 15, 2015 at 16:03
  • @Pspet please try updated answer.
    – Amal Hashim
    Commented Jan 15, 2015 at 16:06
  • Amal, I have copied your revised formula and retyped it and it is still not working. Any other leads?
    – Pspet
    Commented Jan 15, 2015 at 16:36
  • @Pspet i forgot to place IF statements. Please try updated formula. You can copy paste instead of typing.
    – Amal Hashim
    Commented Jan 15, 2015 at 16:55
  • Amal, that worked!!! THANK YOU SO MUCH!!! I will create my other 2 calculated fields [Order Date Summary] and [Arrival Date Summary] using the same premise. Then I will create the formula for the [Date Summary]. I truly appreciate your help. Hope you don't mind if I write back if I get stuck. You are awesome!!!
    – Pspet
    Commented Jan 15, 2015 at 17:08

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.