Calculating age in months

I'm testing smartsheet to see if it can do better than the current excel spreadsheet that is becoming unwieldly with too many users.

Basically I'm replicating the current structure I have in excel which tracks recommendations made, responses to those recommendations, and implementation dates. I need to track when implementation of a recommendation is overdue, in months. This is where my problem lies, I need a good substitute for the following formula:

=IF(A="complete", "", IF(C2="", "??"", IF(C2 > TODAY(), 0, DATEDIF(C2, TODAY(), "M")))))

I am new to smartsheet, but don't think there is an equivalent to the DATEDIF function in Excel. Basically I want the formula, if all other parameters are met, to count the number of months a recommendation is overdue.

Cheers

Find more posts tagged with

Formulas

Comments

John Sauber

It depends on what you mean by "number of months." It looks like the DATEDIF() function in excel, when using the "M" parameter, doesn't care about where in the month the dates fall. So DATEDIF(1/31/2015, 2/1/2015, "M") results in a value of 1, even though there is only 1 day difference (which is about 0.03 months). It also appears that it can't handle negative differences, which doesn't seem very robust or useful to me, but I don't know what context you're working in.

The formula below is obviously for row 1, and column "c" is a date-type column. It works whether today() is before or after the date found in column "c" which would detect both overdue, and tasks completed early:

=IF(YEAR(TODAY()) > YEAR(c1), 12 * (YEAR(TODAY()) - YEAR(c1)) + MONTH(TODAY()) - MONTH(c1), 12 * (YEAR(c1) - YEAR(TODAY())) + MONTH(c1) - MONTH(TODAY()))

You can extend this to include logic about the % Complete as well, if applicable, as 100% would likely trump calling something overdue.

The simple option would be to opt for the approximation of months (where you can get decimal detail) by doing:

=(today()-c1)/30

John - thanks for responding. I'm not sure I follow your suggestion.

Basically I currently have a spreadsheet where the status in one column is dependent on other columns. The above formula, which I see now I wrote incorrectly, it should have been: =IF(A2="complete", "", IF(C2="", "??"", IF(C2 > TODAY(), 0, DATEDIF(C2, TODAY(), "M"))))), is going into cell D2.

Is your suggested formula a replacement for IF(C2 > TODAY(), 0, DATEDIF(C2, TODAY(), "M"))))), or is it a replacement fo the entire formula?

Also, I agree about your point re: "number of months", to be frank, for my purposes I start looking for follow-up after 2,3, or 4 months, so I am not concerned so much about something that is 1 month overdue, because as you say, it could only be 1 day overdue

John Sauber

My formula was only replacing the DATEDIF() function. The logic surrounding that is up to you to figure out.

It's a drop-in replacement so copy my entire formula except for the equals sign, and highlight just DATEDIF(C2, TODAY(), "M") in your formula and paste it in. As I said, mine is referencing row 1, and yours seems to be referencing row 2, so be sure to handle that.

Without validating it, I would guess row 2 would be:

=IF(A2="complete", "", IF(C2="", "??"", IF(C2 > TODAY(), 0, IF(YEAR(TODAY()) > YEAR(c2), 12 * (YEAR(TODAY()) - YEAR(c2)) + MONTH(TODAY()) - MONTH(c2), 12 * (YEAR(c2) - YEAR(TODAY())) + MONTH(c2) - MONTH(TODAY()))))))