Formula to add additional year or years to a date referencing another column in the Grid

Bert Hoffbeck

We have about 17 different training activities that we track for each employee.

Upon completion of the training we enter the certification date for that type of training.

Most of these certification expire after1 year, 3 years, or never (one time certification).

I'd like to be able to use a formula to add either 1 year, 3 years, etc. to the certification date for a given training type.

Any suggestions on how to approach or a date type formula that will add one year or three years to the certification date (this will be another cell in the grid)?

Amanda Alv

Hi,

I'd start by adding a supporting column, using an "If" formula to designate the amount of years you need added. For example, =VALUE(IF([Certification]@row = "Certabc", "1", IF([Certification]@row = "certxyz", "3"))

Next, In the column that will represent the end of the certification period, use the formula:

=DATE(YEAR([Certification Date]@row) + [support column]@row, MONTH([Certification Date]@row), DAY([Certification Date]@row))

Hope that helps!

Amanda Alv

Hi,

I'd start by adding a supporting column, using an "If" formula to designate the amount of years you need added. For example, =VALUE(IF([Certification]@row = "Certabc", "1", IF([Certification]@row = "certxyz", "3"))

Next, In the column that will represent the end of the certification period, use the formula:

=DATE(YEAR([Certification Date]@row) + [support column]@row, MONTH([Certification Date]@row), DAY([Certification Date]@row))

Hope that helps!

Bert Hoffbeck

Thank you Amanda!

SerRosie

I'm trying to use the same formula and reference the support column but get #Invalid Data Type error.

=DATE(YEAR([BLINK UNIT 1 EVC COMMISSIONING DATE]@row) + [BLINK STANDARD YEAR(S)]@row, MONTH([BLINK UNIT 1 EVC COMMISSIONING DATE]@row), DAY([BLINK UNIT 1 EVC COMMISSIONING DATE]@row))

Is there an error in my formula I'm not seeing?

Genevieve P.

Hi @SerRosie

I can see that the number in your column "BLINK STANDARD YEAR(S)" is appearing on the left side of the cell, meaning it's being read as text and not numerical. Can you identify what formula you're using in that column?

You could wrap it in a VALUE(---) function to turn it into a number, then your other formula should work!

Cheers,

Genevieve

SerRosie

Thank you Genevieve, that was the perfect solution!!