Added 2 years to date, only want to populate in specific fields

I have created a calculation to add 2 years to a specific date listed in a field, but I would like to create a formula where it calculates the 2 year date but only populates if the other field is filled in.

Current formula:

=DATE(YEAR([PO Date]@row ) + 2, MONTH([PO Date]@row ), DAY([PO Date]@row ))

If the PO date is fill in, then I would like to populate the Expiration Date field where it adds 2 years to the PO date, but only populates if there is a PO date listed.

Find more posts tagged with

Formulas

Accepted answers

Anjanesh Vaidya

Hi @Angela L
Please use the formula below.

=IF(NOT(ISBLANK([PO Date]@row )), DATE(YEAR([PO Date]@row ) + 2, MONTH([PO Date]@row ), DAY([PO Date]@row )), "")

With this formula, the date will appear in the Expiration Date column only when the PO Date is entered. Otherwise, the cell will remain blank as per the requirement.

Paul Newcome

If you do not need dependencies and whatnot, switch to Gantt view, click on the settings icon in the top right corner, and then turn the dependencies off.

This should allow you to apply formulas to whichever column(s) / row(s) you need.

All comments

Anjanesh Vaidya

Hi @Angela L
Please use the formula below.

=IF(NOT(ISBLANK([PO Date]@row )), DATE(YEAR([PO Date]@row ) + 2, MONTH([PO Date]@row ), DAY([PO Date]@row )), "")

With this formula, the date will appear in the Expiration Date column only when the PO Date is entered. Otherwise, the cell will remain blank as per the requirement.

Angela L

Thank you for the information. When I go to add the PO date, I get the below message. I need to be able to add the date to the PO field and then have the Expiration date automatically calculate.

DKazatsky2

Hi @Angela L,

Was this sheet created as a "project" type? If so, was the "PO Date" originally a different column that you renamed?

Paul Newcome

It sounds like you have dependencies turned on using predecessors, duration, etc.. If you do not need predecessors and whatnot, turning the dependencies off should allow you to input the formula.

If you do need dependencies on…

If you can select another date column for the Gantt display, do that.

If you can't select another date column for the Gantt display, you will only be able to adjust the child rows as the message indicates.

Angela L

It is a Grid and the field PO Date is a date field and the calculation is going into another date field.

Paul Newcome

If you do not need dependencies and whatnot, switch to Gantt view, click on the settings icon in the top right corner, and then turn the dependencies off.

This should allow you to apply formulas to whichever column(s) / row(s) you need.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.