Creating an expiration date with a calculated column

Question

_{Thursday, June 29, 2017 5:28 PM}

Hello,

New here and the SP2010.  I have a column with a date and want to set a calculated column with an expiration of 3 years later.  I have that part =TEXT([Last Inspection]+365*3,"mm-yyyy"),

The issue I have is that I want the return to be blank if the is nothing in the Last Inspection column.  I would also like to set colors, Green (just completed), Amber (one year out and Red (inspection due year). 

This is what I have so far but returns an error.  =TEXT([Last Inspection]+365*3,"mm-yyyy"), IIF(ISBLANK[Last Inspection]""),

Thank you in advance.

All replies (10)

_{Saturday, July 1, 2017 8:16 PM ✅Answered}

Hi

this is how the calc columns looks like in my scenario, where Data1 si may datecolumn . Here I use ISBLANK to check if the field is empty ot not ( you can do same think as I said before with the value 1899 )
If necessary, based on your regional settings, replace ";" with ","

Let me know if that help and if you need more details

Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

_{Thursday, June 29, 2017 5:33 PM}

Hi

regarding empty date column, check thius similar post

https://social.msdn.microsoft.com/Forums/office/en-US/73b80dfd-3825-4482-af4e-ff4ddaff23a4/sharepoint-calculated-date-column?forum=sharepointdevelopmentprevious#83ad874d-b808-4b2a-bfcd-81b117ec9bb9

Regarding colors, you can use conditional formatting from SharePoint Designer

Let me know if you need more details

Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

_{Thursday, June 29, 2017 5:51 PM}

I had already looked there and couldn't find my answer.

Thanks

Tim

_{Thursday, June 29, 2017 6:04 PM | 1 vote}

Hi CappsT- FYI: you have "IIF" instead of "IF"

cameron rautmann

_{Thursday, June 29, 2017 6:53 PM | 1 vote}

Hi

use this formula , for a date type column

=IF(ISBLANK(Data1),"empty",Data1+365*3)

where Data1 is your [Last Inspection] column

Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

_{Thursday, June 29, 2017 7:29 PM}

Thank you.

_{Thursday, June 29, 2017 7:38 PM}

Sir, Thank you for continuing to help, I put this in =IIF(ISBLANK(Last Inspection),"empty",Last Inspection+365*3) and it returned a syntax error.

Tim

_{Friday, June 30, 2017 5:42 PM}

I have this to return a DUE value in the column where the Last Inspection is Blank, =IF([Last Inspection]="","DUE",TEXT([Last Inspection]+365*3,"mm-yyyy"))

_{Sunday, July 2, 2017 9:45 AM}

Hi CappsT,

Have you tried Romeo's solution? If it's helpful to you, you can mark as answer.

Thanks,

Dean Wang

Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact [email protected]

_{Monday, July 3, 2017 3:23 PM}

That was right on target