Trying to use IF and OR in combination for given scenerio

Mindfull

Hi,

I have a requirement were I am checking values in 2 column using IF and OR and basis that the formula should return date after pre-defined days. I tried following statement but is it giving #INVALID ARGUMENT error.

=IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), DATE([Start Date]@row + 17), IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), DATE([Start Date]@row + 30)))

Any help on this?

Leibel S

@Prajna Jain

The date function is being used incorrectly and is not needed for this formula.

Try the below function where i removed the DATE() function.

=IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + 17, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), [Start Date]@row + 30))

Leibel S

@Prajna Jain

The date function is being used incorrectly and is not needed for this formula.

Try the below function where i removed the DATE() function.

=IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + 17, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Request"), [Start Date]@row + 30))

Mindfull

@Leibel S Thanks a lot it worked

Mindfull

I am tried to make the above argument more generic and added column reference instead of manually adding number of days.

=IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), DATE([Start Date]@row + Duration@row, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), DATE([Start Date]@row + Duration@row))))

But it is giving #INVALID DATA TYPE error.

Would appreciate quick help on this.

Thanks in anticipation.

Leibel S

@Prajna Jain

you added the date function in again... Remove that and it should work.

Mindfull

@Leibel S wow its is working...thanks.

one more thing that I found problematic is that if the one of the reference columns is blank it is throwing #INVALID OPERATION error.

Thanks again for helping!

Leibel S

@Prajna Jain

Wrap the entire function in an IFERROR()

=IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), [Start Date]@row + Duration@row, IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), [Start Date]@row + Duration@row)),"")

Mindfull

@Leibel S that's great,

I have one more query to bother may be how I can modify/frame this formula so that it does calculate ONLY the working days?

Mindfull

I tried adding WORKDAY function, but giving #INVALID ARGUMENT error:

=IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Request"), [Start Date]@row + (WORKDAY([Start Date]@row, Duration@row)), IF(OR(Level@row = 1, [New/Existing]@row = "Existing Request"), [Start Date]@row + (WORKDAY([Start Date]@row, Duration@row)))))

Any immediate help on this?

Leibel S

See below corrected syntax

=IFERROR(IF(OR(Level@row = 1, [New/Existing]@row = "New Document Request"), WORKDAY([Start Date]@row,Duration@row), IF(OR(Level@row = 2, [New/Existing]@row = "Existing Document Request"), WORKDAY([Start Date]@row,Duration@row))),"")