Formula(s) for calculating past due items on a construction punch list.

Jodi Simoneaux

I'm creating a simple punch list template and would like to add some date calculating functions to it but I'm very unfamiliar with creating date formulas.  I'm attaching a screen shot of this rough template with a sample line already entered.  I would like to create a formula that will tell me how many days past due items added to the list are based on the Date Reported column in relation to today's date.  Anything longer than 5 days would signify it being "past due".  I'm familiar with conditional formatting but I assume I'll need to add a column or 2 to make all of this work.  Can anyone point me in the right direction?

J. Craig Williams

If you want to capture the number of days past due (for display) then you'll need another column. If not, you can just calculate the number and use it in the [Past Due] column.

You'll probably want to not show past due when the [Date Corrected] is filled in.

Something like this should work:

=IF(ISDATE([Date Corrected]@row),"",TODAY() - [Date Reported]@row)

Then in the [Past Due] column, something like this

=IF([Days Past Due]@row > 5,1,0)

Craig

(untested)

Paul Newcome

What Craig said. While it may be "(untested)", it definitely looks like it should be squared away.

J. Craig Williams

Thanks Paul. It was late.

Craig

Paul Newcome

No worries. I saw the time stamp (1:34 am my time) and untested, so I figured I'd at least do a quick count of parenthesis and commas and whatnot. Haha. Even the best can succumb to exhaustion.