Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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]
                )
            )
        )
    )
)
share|improve this answer
    
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 '13 at 14:36
    
No problem. It's not always intuitive either because there is no simple 'switch' statement for calculated columns. –  skeletank Oct 30 '13 at 14:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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