This should be easy!

[lewislusby]

I'm making a department timesheet. The standard working hours (1 day) for 4 employees is 32. I want to put initials of those people who have a day off in another cell. So basically, I want to take away 8 hours for each set of initials in the "holiday/day off" cell. I've made it work but it's messy so is there a quick way? Suggestions please.

 

[Robert Mika]

You can use soemthing like this:

=SUM(a1:a10)-COUNTIF(A1:A10,"DO")

where "DO" is a day off.

 

[lewislusby]

Thanks but can i do it so it counts initials. eg, maybe i'll have "AB,CD" in the holiday cell, so here I'd want 32-8(AB)-8(CD)= 16.

 

[Scott Huish]

Basically you just want to ignore the text in the calculation:

=32-SUM(IF(ISNUMBER(FIND("(",A1:A7)),LEFT(A1:A7,FIND("(",A1:A7)-1)+0))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

Excel Workbook
	A	B	C	D	E	F
1	8(AB)					16
2	8(CD)
3
4
5
Sheet1

 

[Scott Huish]

It would be easier though, if you put the values in the parentheses in a different cell rather than the same one.

 

[lewislusby]

Either "AB","AB,CD","AB,CD,EF" will be in the cell depending on how many people take holiday, no numerical values. So.....

 

[Scott Huish]

So any alpha sequences counts as 8?

=32-(countif(a1:a7,"??")*8)

 

[lewislusby]

Thats great for 1 set of initials but i need to cover all combinations and

=32-((COUNTIF(C5,("AB"))*8)+(COUNTIF(C5,("CD"))*8)+(COUNTIF(C5,("EF"))*8)+(COUNTIF(C5,("GH"))*8))

doesn't seem to work. Apologies for my uselessness in advance.

Thanks

 

[Scott Huish]

My formula works no matter what the intials are. What's the problem?