1 Attachment(s)
Test Attachments and stuff :)
Attachment 1686
Hi Admin
I was a bit confused at first.
I see what you have done now.
You have taken his AFTER and applied a SUMIF on the two columns I and J....
So first , You added then the extra column K with H for Holiday and N for normal day, | Row\Col |
E |
F |
G |
H |
I |
J |
K |
|---|
1 |
21.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
N |
2 |
22.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
N |
3 |
23.Dec.16 |
7:00 |
15:00 |
8:00 |
|
8:00 |
H |
4 |
24.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
N |
5 |
25.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
N |
Then you have these formulas:| Row\Col |
G |
H |
I |
J |
|---|
35 |
=SUMIF(K1:K31,"N",J1:J31) |
|
|
=SUMIF(K1:K31,"H",J1:J31) |
If Nelson wanted to do that he would still need to use part of my code to get the AFTER worksheet in it final form, as his start point , the BEFORE File is a bit different and needs a bit of manipulation based on his various criteria. | Row\Col |
E |
F |
G |
H |
I |
J |
|---|
1 |
21.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
2 |
22.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
3 |
23.Dec.16 |
7:00 |
15:00 |
8:00 |
9:00 |
0:00 |
4 |
24.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
5 |
25.Dec.16 |
7:00 |
18:00 |
11:00 |
9:00 |
2:00 |
_...........
By the way, I originally assumed that Holiday was a "Holy Friday thing", but I am not sure if that is correct. What I will do in the next post to cover my back and to let your solution to work is do another code, which checks based on the background colour being yellow for a holiday, then does the manipulation of the Worksheet, but also includes then your H or N, and while I am at it pastes in your formulas:
I expect then Nelson will have more than enough to keep him busy or confuse him :)
Alan
