How to fix INVAID DATA TYPE, referencing start and end dates, & duration to calculate % complete

KristyQu

I am using a project sheet with the formula below to calculate the % complete based on the duration of the task, and start and finish dates. However, if there are not dates in the start and finish column I am getting an INVALID DATA TYPE error. I've tried using IFERROR in my equation but I cannot get it to work. I plan to share this sheet for others to fill in and would like to get rid of the error message. Please help.

=IF(Start@row <= TODAY(), NETWORKDAYS(Start@row, MIN(TODAY(), Finish@row)) / Duration@row, 0)

Samuel Mueller

@KristyQu thank you for sharing that. And I apologize I misread your questions. Try this -

=if(or(start@row="",finish@row=""),"",IF(Start@row <= TODAY(), NETWORKDAYS(Start@row, MIN(TODAY(), Finish@row)) / Duration@row, 0))

Samuel Mueller

@KristyQu what are you trying to achieve by dividing by the duration? I think that may be your issue.

KristyQu

Hello, the idea is to have my % complete column to automatically update to represent progress of each task, based on duration over the start and finish dates. I'm trying to get the sheet to do as much of the updates as possible without changing too many cells. This sheet will be used by team members within my organization that are not the most computer savvy. Below is a draft of the sheet, thanks.

.

Samuel Mueller

@KristyQu thank you for sharing that. And I apologize I misread your questions. Try this -

=if(or(start@row="",finish@row=""),"",IF(Start@row <= TODAY(), NETWORKDAYS(Start@row, MIN(TODAY(), Finish@row)) / Duration@row, 0))

KristyQu

That fixed it, thank you!

Samuel Mueller

You're welcome! let me know if you need anything else.

Ipshita

@KristyQu

It is also a best practice to always wrap your formula with the =IFERROR( wherever there are chances of getting invalid data type, i.e. there are either blanks in your cells or data that can be read incorrectly by the system.

Hope this helps,

Cheers!

Ipshita