# Excel formula for calculating work days based on number of resources

I have an Excel spreadsheet cell which looks as follows:

``````   A             | B           | C                    | D              | E
1  TASK          | WORK (days) | RESOURCES            | PLUMBER (days) | ELECTRICIAN (days)
2  Fit bathroom  | 3           | Plumber, Electrician | 1.5            | 1.5
``````

Cells A2, B2 and C2 are entered manually.

The formula in cell D2 takes the total number of "Work" days (cell B2) and divides them by the number of resources specified in cell C2 (each resource is separated by a comma):

``````=IF(ISNUMBER(SEARCH("Plumber",\$C2)),\$B2/(LEN(\$C2)-LEN(SUBSTITUTE(\$C2,",",""))+1),0)
``````

The formula for cell E2 is similar:

`````` =IF(ISNUMBER(SEARCH("Electrician",\$C2)),\$B2/(LEN(\$C2)-LEN(SUBSTITUTE(\$C2,",",""))+1),0)
``````

I'd like to modify my forumla to be able to deal with the following kind of entry in the C2 cell:

``````Plumber, Electrician [200%]
``````

In this case, cell D2 should be set to 1 and cell E2 should be set to 2.

I hope this makes sense and would appreciate any help on how to achieve this.

• Considering possible complexity of inputs ("plumber [150%], electrician" and "plumber [50%], electrician, carpenter [120%]" ...) I don't suggest doing it with worksheet function, far more easy creating a function in VBA for that. – Máté Juhász Apr 1 '16 at 13:37

This macro function will work:

``````Public Function resourceDays(resourceName As String, totalDays As Integer, totalResources As String)
resourceDays = 0
Dim eachResource() As String
eachResource = Split(totalResources, ",")
totalValues = 0
For i = 0 To UBound(eachResource)
thisresource = eachResource(i)
startPct = InStr(thisresource, "[")
If startPct = 0 Then
resourceValue = 1
totalValues = totalValues + resourceValue
If UCase(Trim(thisresource)) = UCase(Trim(resourceName)) Then
thisvalue = resourceValue
End If
Else
endPct = InStr(thisresource, "%")
PctValue = (Mid(thisresource, startPct + 1, endPct - startPct - 1)) / 100
Tempresource = Mid(thisresource, 1, startPct - 1)
resourceValue = PctValue
totalValues = totalValues + resourceValue
If UCase(Trim(Tempresource)) = UCase(Trim(resourceName)) Then
thisvalue = resourceValue
End If

End If
Next i
resourceDays = totalDays * (thisvalue / totalValues)
End Function
``````

Open VBA / Macros with Alt+ F11, under ThisWorkbook insert a new module and paste the code on the right side.

On cell D2 put the formula `=resourceDays(D1,B2,C2)`, where the parameters are

`resourceDays=(Name of Resource, Total Of Days, String Of Total Resources)`.

As Mate Juhasz pointed out, it is very hard to do it with worksheet function, but not impossible.

If you really want to do it then you had better use temporary variable (Use some cells to store temporary value) then hide them. (for example, set width of column G = 0)

You can do that like this:

``````G1 =SEARCH(",", C2)
G2 =TRIM(MID(C2,G1+1,LEN(C2)))
G3 =TRIM(MID(C2,G1+1,LEN(C2)))
G4 =MID(G2, SEARCH("[",G2)+1, SEARCH("]", G2)-SEARCH("[",G2)-1)
G5 =MID(G3, SEARCH("[",G3)+1, SEARCH("]", G3)-SEARCH("[",G3)-1)
G6 =IF(ISNUMBER(G4+0),G4+0,1)
G7 =IF(ISNUMBER(G5+0),G5+0,1)
G8 =B2 / (G6+G7) * G6
G9 =B2 / (G6+G7) * G7

D3 =IF(ISNUMBER(SEARCH("Plumber",G3)), G9, G8)
E3 =B2-D2
``````

If you don't want to use these temporary variable then you can replace every cell with its value, but formula would be very long.

However, just as Mate Juhasz pointed, that is very hard. Use macro is better.

Achieving your goal will be really complicated as other mates mentioned. A better way to achieve your goal is to think numerically. I see if you change the way of cells working will be more effective. For instance, make column D & E manually modified. While column C has IF statement that will search in D and E columns for numbers input. This will make it easier for you to modify or add any other functions within the sheet. This is an example of what I meant :

``````=IF(AND(ISNUMBER(D3),ISNUMBER(E3)),"Plumber, Electrician",IF(ISNUMBER(D3),"Plumber",IF(ISNUMBER(E3),"Electrician","")))
``````

When you use the above function in column C, it will search for number input in column D and E, and put the correct resources. From the same function, you can also extend it further.

The reason that I changed the way of input, is because Excel meant to be working with numbers, and also typing numbers is faster that typing strings, as you will save time. Think about it again.