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].

0

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

Check this post

I did not get your second question.

  • 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 Jan 15 '15 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.

  • 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 Jan 15 '15 at 16:03
  • @Pspet please try updated answer. – Amal Hashim Jan 15 '15 at 16:06
  • Amal, I have copied your revised formula and retyped it and it is still not working. Any other leads? – Pspet Jan 15 '15 at 16:36
  • @Pspet i forgot to place IF statements. Please try updated formula. You can copy paste instead of typing. – Amal Hashim Jan 15 '15 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 Jan 15 '15 at 17:08

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.