PLEASE HELP! formula to calculate % of completeness between two dates

j_logiurato1400

Hi All!

I am looking for help with a formula to calculate % of completeness between two dates in my sheet. After inputting the formula, I am getting the #INVALIDDATATYPE. I have checked that all of my columns are date-assigned. Can someone please help me? Here is the formula that I am using with a screenshot of my sheet.

Thanks in Advance!

Peggy Parchert

Hello @j_logiurato1400

Hope you are well. The formula you show has a #REF in it - what is that supposed to be?

Peggy

Paul Newcome

Give this a try:

=MAX(MIN((TODAY() - [Actual End Date]@row) / ([Actual Start Date]@row - [Actual End Date]@row), 1), 0)

Keep in mind that the above will only work when you have dates in both the actual start and actual end date columns. If you need to incorporate the expected start and end dates, we will need the logic on how you want them tied in.

j_logiurato1400

@Paul Newcome unfortunately, even with the dates added to the actual start and actual end date, I am still getting #UNPARSEABLE

j_logiurato1400

@Peggy Parchert, this sheet is part of a template bundle and the REF was referencing a report and dashboard (both of which I haven't started yet). I took out the REF and I still have the same issue. Hmmm....

j_logiurato1400

@Paul Newcome

Paul Newcome

Make sure you are using the column names that you actually have in your sheet.

j_logiurato1400

@Paul Newcome we're getting a little closer....?

Paul Newcome

Are you getting an error?

Peggy Parchert

@j_logiurato1400

Is your % Complete column set for % format? I got @Paul Newcome formula to work in my test sheet.

You also might need to add an IFERROR for those blank dates.

=IFERROR(MAX(MIN((TODAY() - [Actual End]@row) / ([Actual Start]@row - [Actual End]@row), 1), 0), "")

Peggy

Paul Newcome

@Peggy Parchert I had planned an IFERROR once we got the base working. Also wanted to leave some room open for adjustment in case we needed to work in the expected dates.