2

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.

1 Answer 1

3

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]
                )
            )
        )
    )
)
2
  • That's sorted my issue, can't believe it turned out as simple as that in the end and that I couldn't figure it out. I think I was overthinking it. Thank you very much.
    – daryl
    Oct 30, 2013 at 14:36
  • No problem. It's not always intuitive either because there is no simple 'switch' statement for calculated columns.
    – skeletank
    Oct 30, 2013 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.