# Need help with a date calculation formula

✭✭✭✭

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

• ✭✭✭✭

Thanks, I'll try that.

• ✭✭✭✭✭

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}) )`

```= 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.

• ✭✭✭✭

Thanks, I'll try that.

• ✭✭✭✭

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.

• ✭✭✭✭✭

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}))`

```= 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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!