Adding a number of months to a date using formulas

03/19/20
Accepted

I have a calibration sheet that lists the number of months required between calibration dates and a column that states when the last calibration was carried out. I would like to use these 2 columns to calculate the date when the equipment next requires calibration. Can anyone help?

Thanks,

Colin


Best Answer

Previous1

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I recently helped someone else here in the community with adding n months to a date. Let me dig around and see if I can find the link.

  • Thanks Paul, worked a treat.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

  • My friend and I came up with this:


    =IF(ISBLANK([EMROLLMENT DATE - NOMs: Initial Date Completed]@row), "TBD", IF(MONTH([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) < 10, DATE(YEAR([EMROLLMENT DATE - NOMs: Initial Date Completed]@row), MONTH([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) + 3, DAY([EMROLLMENT DATE - NOMs: Initial Date Completed]@row)), DATE(YEAR([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) + 1, MONTH([EMROLLMENT DATE - NOMs: Initial Date Completed]@row) - 9, DAY([EMROLLMENT DATE - NOMs: Initial Date Completed]@row))))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Jen Eldridge It looks like that would only be for adding 3 months and not a variable amount of months that could be stored in a separate column. Am I reading it correctly?

  • Hi, I have a date column and I want to generate a new date by adding 6 months to the date. I have used this formula-

    =DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 1, DAY([Go Live Date in V2]@row))

    but its only working within the year when date lays in next year its giving me an error so can you help me with that thanks.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @gourav To add six months, it would look something like this...

    =IFERROR(DATE(YEAR([Go Live Date in V2]@row), MONTH([Go Live Date in V2]@row) + 6, DAY([Go Live Date in V2]@row)), DATE(YEAR([Go Live Date in V2]@row) + 1, MONTH([Go Live Date in V2]@row) - 6, DAY([Go Live Date in V2]@row)))

  • Ed RoseEd Rose
    edited 04/30/21

    Hi Paul

    I am trying to replicate this for an application that is very similar to the calibration one but my attempt at amending the formula is showing as #UNPARSEABLE. Here is a screen shot of the sheet and it relates to the last 3 columns:

    Please can you help me

    Thanks

    Ed

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Ed Rose Exactly which formula are you trying to modify?

  • Hi Paul

    Your original one for Colin Alexander. I am trying to use your formula to automatically generate the Next Test Date by adding Retest Interval - Months to the Last Test Date column figure. I then can create further automation to send me a notification when a retest is required.

    Many thanks

    Ed

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Ed Rose How exactly are you populating the number of months? It looks like they are being stored as text values?

  • Hi Paul. Yes they are text values.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    There's the issue then. You are going to want to have them either stored as numerical values or you are going to need to wrap each instance of that column name in a VALUE function within the formula.

  • It's a drop-down (single select) column. Please can you help me with the correct formula for this.

Sign In or Register to comment.