Need help with a date calculation formula

Options

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

Best Answer

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    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
    Michelle Barnett ✭✭✭✭
    Answer ✓
    Options

    Thanks, I'll try that.

  • Michelle Barnett
    Michelle Barnett ✭✭✭✭
    Options

    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
    Toufong Vang ✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!