Calculated Column - Calculate Date based on other column value

Question

I'm looking for a solution that I'm really struggling to find the answer to (I have searched, plenty!), or more specifically, find an answer that suits my needs. I've read plenty of help including microsoft's own SharePoint help, but no joy.

I would like to have a column in which the date is calculated from the values in two other columns. I have one column named "Calibrated", a second column called "Frequency" and a third column called "Cal Due".

The "Calibrated" column is used by a user to enter the date that a device has been calibrated on, and the "Frequency" column is a Choice type containing one the following values - 1M, 3M, 6M, 1Y, 3Y - which, should be obvious, designate the time inbetween calibrations - 1 month, 3 months, 6 months, 1 year, 3 years.

I need the "Cal Due" column to be calculated so that it updates automatically when the "Calibration" date is changed, and basis the output value on "Calibration" + "Frequency" values. e.g. if "Calibration" equals 30/10/2013 and "Frequency" equals 1M, then "Cal Due" equals 30/11/2013.

As I said, I've read many different solutions based on calculated columns and dates, but all seem to want to add or subtract an integer value to a date instead of using a text value to decide the output.

Any assistance is greatly appreciated.

Answer 1

You can use IF statements to convert the text to integer values:

IF(
    [Frequency]="1 Month",
    DATE(YEAR([Calibration]), MONTH([Calibration]) + 1, DAY([Calibration])),
    IF(
        [Frequency]="3 Months",
        DATE(YEAR([Calibration]), MONTH([Calibration]) + 3, DAY([Calibration])),
        IF(
            [Frequency]="6 Months",
            DATE(YEAR([Calibration]), MONTH([Calibration]) + 6, DAY([Calibration])),
            IF(
                [Frequency]="1 Year",
                DATE(YEAR([Calibration]) + 1, MONTH([Calibration]), DAY([Calibration])),
                IF(
                    [Frequency]="3 Years",
                    DATE(YEAR([Calibration]) + 3, MONTH([Calibration]), DAY([Calibration])),
                    [Calibration]
                )
            )
        )
    )
)