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.

• ✭✭✭✭✭✭

Hello @j_logiurato1400

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

Peggy

• ✭✭✭✭✭✭

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.

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

• @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....

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

Are you getting an error?

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

@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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!