Need help with a date calculation formula

Michelle Barnett

I work with both onshore and offshore resources with different Holidays. I'm trying to calculate how many working days a person would have on a project if they were a US employee or US contractor vs an Offshore employee or Offshore contractor. I'm using the formula below that's turning up blank if I select Offshore employee or offshore contractor. It does show the number of days as long as I select Onshore employee or Onshore contractor.

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

The weird thing is I split US and Offshore holidays into two different sheets because if I had them on one, the US employee number of days was showing too many days off. I tried to get the formula to look at the different references, but it keeps updating to only one of the sheets. - Which formula would I need to work with to get it to evaluate the onshore and offshore as two different values?

Thank you for your help with this.

Michelle

Michelle Barnett

Thanks, I'll try that.

Toufong Vang

Hi, @Michelle Barnett,

Try...

= IF( [Resource Location]@row = "Onshore Employee", NETWORKDAYS([Start Date]@row, [End date]@row, {The_range_that_comprise_US_holidays}), NETWORKDAYS([Start Date]@row, [End date]@row, {The_range_that_comprise_non-US_holidays}) )

In easy-to-read format...

= IF( [Resource Location]@row = "Onshore Employee"
	, NETWORKDAYS([Start Date]@row, [End date]@row, {The_range_that_comprises_US_holidays})
	, NETWORKDAYS([Start Date]@row, [End date]@row, {The_range_that_comprises_NON-US_holidays})
	)

Since there are only two types of employees (Onshore and Offshore) you only need one condition. That is to say, if the employee is not an "onshore employee" then the employee must be an "offshore employee". Therefore, the second method of calculating working-days applies.

Michelle Barnett

Thanks, I'll try that.

Michelle Barnett

Yes, it solved half the problem. I reassigned the ranges on the same column but it's still blank if the resource is an offshore employee or contractor.

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.