I'm getting a formula error on half of this formula

Michelle Barnett

I need to evaluate how many days off a person is going to get depending on their location. Offshore or Onshore. We have Onshore employees and Onshore contractors as well as Offhore employees and Offshore contractors. The formula below evaluates if someone is an Onshore or Offshore resource then uses a range from another sheet to determine the number of working days.

In the formula below, my Onshore employee and Onshore contractor is working. The value appears, but when I select Offshore employee, I get a blank in the field and when I select Offshore Contractor, I get an #invalid data type error. I tried cutting and pasting the functioning part of the formula into the non-functioning part of the formula and just replacing the Onshore with Offshore and the range reference but that didn't work.

Any advice on how I should fix this? - Thanks

=IF(OR(AND([Resource Location]@row = "Onshore Employee"), AND([Resource Location]@row = "Onshore Employee")), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1}), IF(OR(AND([Resource Location]@row = "Onshore Contractor")), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1}), IF(OR(AND([Resource Location]@row = "Offshore Contactor"), AND([Resource Location]@row = "Offshore Contractor")), NETWORKDAYS([Start Date]@row, {2021 HGS Digital Holiday Range 2}, NETWORKDAYS([Start Date]@row, {2021 HGS Digital Holiday Range 2})))))

Leibel S

@Michelle Barnett

you seem to have quite a few AND and OR functions that seem extra. As well, some of your NETWORKDAYS functions have instead of the 'end date' your {2021 HGS Digital Holiday Range 2} range.

Toufong Vang

Hi, @Michelle Barnett

Try the revised formula below.

= IF( CONTAINS("Onshore", [Resource Location]@row), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1}), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 2}))

In easier-to-read format...

= IF( CONTAINS("Onshore", [Resource Location]@row)
	, NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1})
	, NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 2})
	)

If I understand correctly, in the column Resource Location you have 4 resource types: "Onshore Employee", "Onshore Contractor", "Offshore Employee", "Offshore Contractor". The expression CONTAINS("Onshore", [Resource Location]@row) will catch Onshore Employee and Onshore Contractor while excluding Offshore Employee and Offshore Contractor.

Leibel S

@Michelle Barnett

you seem to have quite a few AND and OR functions that seem extra. As well, some of your NETWORKDAYS functions have instead of the 'end date' your {2021 HGS Digital Holiday Range 2} range.

Michelle Barnett

Thanks I needed another set of eyes. I am missing the end date, which is probably why I'm getting the weird error on the offshore contractor option. Thanks for looking at this for me.

Toufong Vang

Hi, @Michelle Barnett

Try the revised formula below.

= IF( CONTAINS("Onshore", [Resource Location]@row), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1}), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 2}))

In easier-to-read format...

= IF( CONTAINS("Onshore", [Resource Location]@row)
	, NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1})
	, NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 2})
	)

If I understand correctly, in the column Resource Location you have 4 resource types: "Onshore Employee", "Onshore Contractor", "Offshore Employee", "Offshore Contractor". The expression CONTAINS("Onshore", [Resource Location]@row) will catch Onshore Employee and Onshore Contractor while excluding Offshore Employee and Offshore Contractor.

Michelle Barnett

Hi Toufong,

Thanks for getting back to me. I got it working Thanks to your help along with Leibel S. Here is the working formula.

=IF(OR(AND([Resource Location]@row = "Onshore Employee"), AND([Resource Location]@row = "Onshore Employee")), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1}), IF(OR(AND([Resource Location]@row = "Onshore Contractor")), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 1}), IF(OR(AND([Resource Location]@row = "Offshore Contactor"), AND([Resource Location]@row = "Offshore Contractor")), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 2}), NETWORKDAYS([Start Date]@row, [End date]@row, {2021 HGS Digital Holiday Range 2}))))

I had to id each option independently then go with the right date range. The last error I got was on the Offshore resources and it turned out when I copied the formula, I left out the end date. Once I got that in place, it calculated correctly.