IF and ISDATE Formula

SJTA

I want to calculate NETDAYS from Submission Date to Todays Date, or to Comments Log Received Date

I have this formula that works.

=IFERROR(IF([Comments Log Received]@row = "", NETDAYS([Date Submitted to SJTA]@row, TODAY()), NETDAYS([Date Submitted to SJTA]@row, [Comments Log Received]@row)), "")

However, I need to expand the formula to calculate what happens after the Comments Log Received becomes a date. This is what I came up with, but it does not work.

=IFERROR(IF([Comments Log Received]@row = "", NETDAYS([Date Submitted to SJTA]@row, TODAY()), NETDAYS([Date Submitted to SJTA]@row, [Comments Log Received]@row)), IF(ISDATE([Comments Log Received]@row, NETDAYS([Date Submitted to SJTA]@row, [SJTA Approval Date]@row), "")))

What am I doing wrong?

Ward.Hively

Hello SJTA,

I can tell you are having some trouble with your IF Statements, it turns out the ISDate function is giving you grief.

I am happy to provide a solution. Please try:

=IFERROR(IF([Comments Log Received]@row = "",NETDAYS([Date Submitted to SJTA]@row, TODAY()), NETDAYS([Date Submitted to SJTA]@row, [Comments Log Received]@row)), IF(ISDATE([Comments Log Received]@row), NETDAYS([Date Submitted to SJTA]@row, [SJTA Approval Date]@row), ""))

As if to say:

If No Comments Have Been Received in the Comment Log

Then return the number of days between Submitted to SJTA and today.

Else return the number of days between the date submitted to SJTA and Comments Log Received

If formula returns an error and Comments Log Received contains a date

Then return the number of days between the Date Submitted to SJTA and SJTA Approval Date

If formula returns an error and Comments Log Received does not contain a date

Then return blank. 

I have provided an attached link if you would like to know more about how I arrived at your solution. Please follow us on LinkedIn if you need any further assistance!

https://www.linkedin.com/feed/update/urn:li:activity:7111446198875553792

SJTA

Thanks but, this does not work. The first half of the formula works, however the IF ISDATE does not change the value.

Ward.Hively

I responded more thoroughly on LinkedIn but, this should work!!!

=IFERROR(IF([Comments Log Received]@row = "", NETDAYS([Date Submitted to SJTA]@row, TODAY()), IF([SJTA Approval Date]@row = "", NETDAYS([Date Submitted to SJTA]@row, [Comments Log Received]@row), NETDAYS([Date Submitted to SJTA]@row, [SJTA Approval Date]@row))), "")