I have a rank of C2 to N2 of which C2 to G2 will represent the days of first
week of the biweekly. Between means of column G and J it represents Saturdays
and Sundays, in H and I respectively. Of the J to the N it is 2nd week of the
biweekly. If the employee in this case, for Tuesday needed all the day, or it
is, D3 cell and that is recorded on his archives of that employee, a single L
is reflected which is equivalent to his labor day of 7.5 hours. But it is
absented without reason Thursday and in this particular situation would apply
a W here (it implies without payment) that is, cell (F3) and in next week
Monday at afternoon retires of their work area (J3) by just disease (SE) 3
hours before the official exit, is described in the table of cumulative
absences an SE3. But on the other day, it absents without giving excuses
certified by a doctor by disease (S). To the end of this line 3, column Z,
must add the S in this case, which could have been with L that is using his
cumulative vacations or LE if they are officially authorized or programmed.
The formula asked must well apply for that line 3 and in the other column
alongside, to say column Z to add the S. On equal results with the LE and SE
should leave total-sum of that biweekly in other separate columns, AA and BB
at the moment like examples.

Monday€¦.Tuesday.Wenday
----
2€¦.C ....... D€¦ €¦.F€¦ €¦. J........K...........Z....AA....BB
----

3€¦ - ........LE€¦. W ....... SE3 .....S€¦. ........3....7,5€¦7.5
----€¦

The idea is to leave totals in different columns for the same biweekly as
much for the L, LE, SE, S, and W (or could be others letters identifying
other kinds of absences like O, F, CL etc) but that when they apply to an
absence of complete day where it does not detail the 7.5hrs of days
accompanied by the letter or letters (in pair) but only when it is several
hours or he leaves part of an hour. Should be clarify in case otherwise on
line 3 there were LE3.5 and L with other letter(s) like SE or/and S for other
days of the week or 2nd week, with other same bare letter in different days
like LE should end up adding 3.5 + 7.5 = 11 on a final balance column for LE.
So the formula must be used on those final columns either for one or another
letter or letters with or without numbers beside them and copy through all
the year(s).
I'll appreciate any help.

hi, (...) ???

if you don't get a *clear* proposal to your post in a reasonably short period of time, try the following:
- back to the spanish news-group
- answer the questions about the holes in your post

regards,
hector.

__ OP __
"JC-PS" escribió en el mensaje ...
I have a rank of C2 to N2 of which C2 to G2 will represent the days of first
week of the biweekly. Between means of column G and J it represents Saturdays
and Sundays, in H and I respectively. Of the J to the N it is 2nd week of the
biweekly. If the employee in this case, for Tuesday needed all the day, or it
is, D3 cell and that is recorded on his archives of that employee, a single L
is reflected which is equivalent to his labor day of 7.5 hours. But it is
absented without reason Thursday and in this particular situation would apply
a W here (it implies without payment) that is, cell (F3) and in next week
Monday at afternoon retires of their work area (J3) by just disease (SE) 3
hours before the official exit, is described in the table of cumulative
absences an SE3. But on the other day, it absents without giving excuses
certified by a doctor by disease (S). To the end of this line 3, column Z,
must add the S in this case, which could have been with L that is using his
cumulative vacations or LE if they are officially authorized or programmed.
The formula asked must well apply for that line 3 and in the other column
alongside, to say column Z to add the S. On equal results with the LE and SE
should leave total-sum of that biweekly in other separate columns, AA and BB
at the moment like examples.

Monday€¦.Tuesday.Wenday
----
2€¦.C ....... D€¦ €¦.F€¦ €¦. J........K...........Z....AA....BB
----

3€¦ - ........LE€¦. W ....... SE3 .....S€¦. ........3....7,5€¦7.5
----€¦

The idea is to leave totals in different columns for the same biweekly as
much for the L, LE, SE, S, and W (or could be others letters identifying
other kinds of absences like O, F, CL etc) but that when they apply to an
absence of complete day where it does not detail the 7.5hrs of days
accompanied by the letter or letters (in pair) but only when it is several
hours or he leaves part of an hour. Should be clarify in case otherwise on
line 3 there were LE3.5 and L with other letter(s) like SE or/and S for other
days of the week or 2nd week, with other same bare letter in different days
like LE should end up adding 3.5 + 7.5 = 11 on a final balance column for LE.
So the formula must be used on those final columns either for one or another
letter or letters with or without numbers beside them and copy through all
the year(s).
I'll appreciate any help.

"Héctor Miguel" wrote:

hi, (...) ???

if you don't get a *clear* proposal to your post in a reasonably short period of time, try the following:
- back to the spanish news-group
- answer the questions about the holes in your post

regards,
hector.

__ OP __
"JC-PS" escribió en el mensaje ...
I have a rank of C2 to N2 of which C2 to G2 will represent the days of first
week of the biweekly. Between means of column G and J it represents Saturdays
and Sundays, in H and I respectively. Of the J to the N it is 2nd week of the
biweekly. If the employee in this case, for Tuesday needed all the day, or it
is, D3 cell and that is recorded on his archives of that employee, a single L
is reflected which is equivalent to his labor day of 7.5 hours. But it is
absented without reason Thursday and in this particular situation would apply
a W here (it implies without payment) that is, cell (F3) and in next week
Monday at afternoon retires of their work area (J3) by just disease (SE) 3
hours before the official exit, is described in the table of cumulative
absences an SE3. But on the other day, it absents without giving excuses
certified by a doctor by disease (S). To the end of this line 3, column Z,
must add the S in this case, which could have been with L that is using his
cumulative vacations or LE if they are officially authorized or programmed.
The formula asked must well apply for that line 3 and in the other column
alongside, to say column Z to add the S. On equal results with the LE and SE
should leave total-sum of that biweekly in other separate columns, AA and BB
at the moment like examples.

Monday€¦.Tuesday.Wenday
----
2€¦.C ....... D€¦ €¦.F€¦ €¦. J........K...........Z....AA....BB
----

3€¦ - ........LE€¦. W ....... SE3 .....S€¦. ........3....7,5€¦7.5
----€¦

The idea is to leave totals in different columns for the same biweekly as
much for the L, LE, SE, S, and W (or could be others letters identifying
other kinds of absences like O, F, CL etc) but that when they apply to an
absence of complete day where it does not detail the 7.5hrs of days
accompanied by the letter or letters (in pair) but only when it is several
hours or he leaves part of an hour. Should be clarify in case otherwise on
line 3 there were LE3.5 and L with other letter(s) like SE or/and S for other
days of the week or 2nd week, with other same bare letter in different days
like LE should end up adding 3.5 + 7.5 = 11 on a final balance column for LE.
So the formula must be used on those final columns either for one or another
letter or letters with or without numbers beside them and copy through all
the year(s).
I'll appreciate any help.



Hello, Hector.
I'm trying to be specific and clear of what I'm looking for this time. I appreciate your help.

hi. (...) ???

I'm trying to be specific and clear of what I'm looking for this time. I appreciate your help.

as far as i can see, you are still posting unclear "details" regarding your data layout (i.e.)

- *clear*:

columns C to N are be-weekly representation of employee's absence of labour
representation includes letters (single or combined) and the possibility of numbers

- *unclear*:

which are (ALL) the letters used for any representation and what the meaning of each ?
note your post in this part:
"... as much for the L, LE, SE, S, and W (or could be others letters identifying other kinds of absences like O, F, CL etc)"
- "other letters" AND "etc" opens a wider scenario to work with... -???-

where (exactly) do you need a summary of the value-converted "representation" of WHAT letters (combination and numbers) ?
(could you provide a more "viewable" details ?... cells, columns, range, sample data, formulae, etc.)

regards,
hector.

__ previous __
if you don't get a *clear* proposal to your post in a reasonably short period of time, try the following:
- back to the spanish news-group
- answer the questions about the holes in your post

"Héctor Miguel" wrote:

hi. (...) ???

I'm trying to be specific and clear of what I'm looking for this time. I appreciate your help.

as far as i can see, you are still posting unclear "details" regarding your data layout (i.e.)

- *clear*:

columns C to N are be-weekly representation of employee's absence of labour
representation includes letters (single or combined) and the possibility of numbers

- *unclear*:

which are (ALL) the letters used for any representation and what the meaning of each ?
note your post in this part:
"... as much for the L, LE, SE, S, and W (or could be others letters identifying other kinds of absences like O, F, CL etc)"
- "other letters" AND "etc" opens a wider scenario to work with... -???-

where (exactly) do you need a summary of the value-converted "representation" of WHAT letters (combination and numbers) ?
(could you provide a more "viewable" details ?... cells, columns, range, sample data, formulae, etc.)

regards,
hector.

__ previous __
if you don't get a *clear* proposal to your post in a reasonably short period of time, try the following:
- back to the spanish news-group
- answer the questions about the holes in your post


Hi, Hector.
The table of Regular Register Licenses should be like shown here afterward. The licenses of other kinds like LF, SF, WF, A, B, M, O, U and CL with hours used by the employee are posted on other table not in the main one of L, LE, S, SE & W. The possibility of numbers beside a letter or two should be considered here in the summation too. The main table should look like a week format days on a two week bases or be-weekly, and a regular calender from January to December of the current year right on the left side of the table downward (from line A3 to A29). Line 3 starting on C column is the first cell or Monday of January alongside up to Friday of the second week for the first be-weekly of that month. Totals of hours used by worker on those weeks either for L, LE or S, SE and even W should end up to a column aside in the same order of letters shown:Q, R, W, X and AA for the "Ws".
Example:
....A......................B..........C........D.. .........J.....
2........................Monday.Tues...Wends...Fri day
3.01/12/08............-............L.........LE.........S....

...... ...K.........N..........Q..............R.......... ..W.......
2....Monday...Friday...VacationL..VacationLE..Sick-S
3....W..........LE3.5.....7.5............11....... .....7.5...

......X.........W ..... -----------------------------------
Sick-SE.....No Pay....---------------------------------
......-.........7.5..... -----Note that those day present are show just
with a "-" meaning no license used, like on B3 and the other days not shown
here.------------------------------

I hope might be helpful, the first time trying making a table outside Excel is badly awful. Any question be glad to answer it.

hi (...), ???

this is what i "can see"...
data:
[C3] -
[D3] L
[E3] LE
[G3] S
[J3] W
[N3] LE3.5

this is what you post as needed:
results:
[Q3] 7.5
[R3] 11
[W3] 7.5
[X3] -
[AA3] 7.5

suggested formulae:

[Q3] =countif(c3:n3,"l")*7.5
[R3] =countif(c3:n3,"le")*7.5+sumproduct(if(left(c3:n3, 2)="le",--substitute(lower(c3:n3),"le",0)))
the above is an array-formula ctrl + shift + enter
[W3] =countif(c3:n3,"s")*7.5
[X3] =countif(c3:n3,"se")*7.5
[AA3] =countif(c3:n3,"w")*7.5

please, revise and adapt/modify suggested array-formula in [R3] according other "expected" representacion (letters & numbers)

hth,
hector.

__ OP __
The table of Regular Register Licenses should be like shown here afterward.
The licenses of other kinds like LF, SF, WF, A, B, M, O, U and CL with hours used by the employee are posted on other table not in the main one of L, LE, S, SE & W.
The possibility of numbers beside a letter or two should be considered here in the summation too.
The main table should look like a week format days on a two week bases or be-weekly, and a regular calender from January to December of the current year right on the left side of the table downward (from line A3 to A29).
Line 3 starting on C column is the first cell or Monday of January alongside up to Friday of the second week for the first be-weekly of that month. Totals of hours used by worker on those weeks either for L, LE or S, SE and even W should end up to a column aside in the same order of letters shown:Q,
R, W, X and AA for the "Ws".
Example:
...A......................B..........C........D... ........J.....
2........................Monday.Tues...Wends...Fri day
3.01/12/08............-............L.........LE.........S....

..... ...K.........N..........Q..............R.......... ..W.......
2....Monday...Friday...VacationL..VacationLE..Sick-S
3....W..........LE3.5.....7.5............11....... .....7.5...

.....X.........W ..... -----------------------------------
Sick-SE.....No Pay....---------------------------------
.....-.........7.5..... -----Note that those day present are show just with a "-" meaning no license used, like on B3 and the other days not shown here.------------------------------

I hope might be helpful, the first time trying making a table outside Excel is badly awful.
Any question be glad to answer it.

Thanks Hector, up to the moment the option formula,
=countif(c3:n3,"le")*7.5+sumproduct(if(left( c3:n3,2)="le",
--substitute(lower(c3:n3),"le",0))) with CSE smoothly doing its
work.
_____---___---____

data:
[C3] -
[D3] L
[E3] LE
[G3] S
[J3] W
[N3] LE3.5

this is what you post as needed:
results:
[Q3] 7.5
[R3] 11
[W3] 7.5
[X3] -
[AA3] 7.5

suggested formulae:

[Q3] =countif(c3:n3,"l")*7.5
[R3] =countif(c3:n3,"le")*7.5+sumproduct(if(left(c3:n3, 2)="le",--substitute(lower(c3:n3),"le",0)))
the above is an array-formula ctrl + shift + enter
[W3] =countif(c3:n3,"s")*7.5
[X3] =countif(c3:n3,"se")*7.5
[AA3] =countif(c3:n3,"w")*7.5

please, revise and adapt/modify suggested array-formula in [R3] according other "expected" representacion (letters & numbers)

hth,
hector.

__ OP __
The table of Regular Register Licenses should be like shown here afterward.
The licenses of other kinds like LF, SF, WF, A, B, M, O, U and CL with hours used by the employee are posted on other table not in the main one of L, LE, S, SE & W.
The possibility of numbers beside a letter or two should be considered here in the summation too.
The main table should look like a week format days on a two week bases or be-weekly, and a regular calender from January to December of the current year right on the left side of the table downward (from line A3 to A29).
Line 3 starting on C column is the first cell or Monday of January alongside up to Friday of the second week for the first be-weekly of that month. Totals of hours used by worker on those weeks either for L, LE or S, SE and even W should end up to a column aside in the same order of letters shown:Q,
R, W, X and AA for the "Ws".
Example:
...A......................B..........C........D... ........J.....
2........................Monday.Tues...Wends...Fri day
3.01/12/08............-............L.........LE.........S....

..... ...K.........N..........Q..............R.......... ..W.......
2....Monday...Friday...VacationL..VacationLE..Sick-S
3....W..........LE3.5.....7.5............11....... .....7.5...

.....X.........W ..... -----------------------------------
Sick-SE.....No Pay....---------------------------------
.....-.........7.5..... -----Note that those day present are show just with a "-" meaning no license used, like on B3 and the other days not shown here.------------------------------

I hope might be helpful, the first time trying making a table outside Excel is badly awful.
Any question be glad to answer it.

hi, !

... the... formula, =countif(c3:n3,"le")*7.5+sumproduct(if(left(c3:n3, 2)="le",--substitute(lower(c3:n3),"le",0)))
with CSE smoothly doing its work...

glad to be in help, thanks for the feed-back

regards,
hector.

p.s. don't forget what you just learned in "Hints and Tips for New Posters" ;)

"Héctor Miguel" wrote:

hi (...), ???

this is what i "can see"...
data:
[C3] -
[D3] L
[E3] LE
[G3] S
[J3] W
[N3] LE3.5

this is what you post as needed:
results:
[Q3] 7.5
[R3] 11
[W3] 7.5
[X3] -
[AA3] 7.5

suggested formulae:

[Q3] =countif(c3:n3,"l")*7.5
[R3] =countif(c3:n3,"le")*7.5+sumproduct(if(left(c3:n3, 2)="le",--substitute(lower(c3:n3),"le",0)))
the above is an array-formula ctrl + shift + enter
[W3] =countif(c3:n3,"s")*7.5
[X3] =countif(c3:n3,"se")*7.5
[AA3] =countif(c3:n3,"w")*7.5

please, revise and adapt/modify suggested array-formula in [R3] according other "expected" representacion (letters & numbers)

hth,
hector.

__ OP __
The table of Regular Register Licenses should be like shown here afterward.
The licenses of other kinds like LF, SF, WF, A, B, M, O, U and CL with hours used by the employee are posted on other table not in the main one of L, LE, S, SE & W.
The possibility of numbers beside a letter or two should be considered here in the summation too.
The main table should look like a week format days on a two week bases or be-weekly, and a regular calender from January to December of the current year right on the left side of the table downward (from line A3 to A29).
Line 3 starting on C column is the first cell or Monday of January alongside up to Friday of the second week for the first be-weekly of that month. Totals of hours used by worker on those weeks either for L, LE or S, SE and even W should end up to a column aside in the same order of letters shown:Q,
R, W, X and AA for the "Ws".
Example:
...A......................B..........C........D... ........J.....
2........................Monday.Tues...Wends...Fri day
3.01/12/08............-............L.........LE.........S....

..... ...K.........N..........Q..............R.......... ..W.......
2....Monday...Friday...VacationL..VacationLE..Sick-S
3....W..........LE3.5.....7.5............11....... .....7.5...

.....X.........W ..... -----------------------------------
Sick-SE.....No Pay....---------------------------------
.....-.........7.5..... -----Note that those day present are show just with a "-" meaning no license used, like on B3 and the other days not shown here.------------------------------

I hope might be helpful, the first time trying making a table outside Excel is badly awful.
Any question be glad to answer it.


One thing, Hector.
When I have an L in the same line with LE, formula goes #VALUE!. The formula
works well with other letters in the same line but not beginning with the
same one. Example:
19.....L3 L2.5 LE7.5 LE7.5 LE7.5 LE7.5 LE7.5 H = VacationL 5.5

the formula should sum L3 + L2.5 and disregard for VacationL Totals those LEs.

Hi, Hector. I wrote before;
" When I have an L in the same line with LE, formula goes #VALUE!. The formula
works well with other letters in the same line but not beginning with the
same one. Example:
19.....L3 L2.5 LE7.5 LE7.5 LE7.5 LE7.5 LE7.5 H = VacationL 5.5

the formula should sum L3 + L2.5 and disregard for VacationL Totals those LEs." I change the 2 number in SUMPRODUCT(IF(LEFT(C19:N19,2)="l", by one but just shows "0" on total. I wipe up those numbers beside the letters LE but doesn't make any changes. I have no hint of how to get it better ...

hi, !

Hi, Hector. I wrote before;
" When I have an L in the same line with LE, formula goes #VALUE!. The formula
works well with other letters in the same line but not beginning with the
same one. Example:
19.....L3 L2.5 LE7.5 LE7.5 LE7.5 LE7.5 LE7.5 H = VacationL 5.5

the formula should sum L3 + L2.5 and disregard for VacationL Totals those LEs."
I change the 2 number in SUMPRODUCT(IF(LEFT(C19:N19,2)="l", by one but just shows "0" on total.
I wipe up those numbers beside the letters LE but doesn't make any changes. I have no hint of how to get it better ...

formula in Q column becomes an array-formula :-((

[Q19] =countif(c19:n19,"l")*7.5+sum(if(isnumber(--substitute(lower(c19:n19),"l",0)),--substitute(lower(c19:n19),"l",0)))

or, from the beginning...

[Q3] =countif(c3:n3,"l")*7.5+sum(if(isnumber(--substitute(lower(c3:n3),"l",0)),--substitute(lower(c3:n3),"l",0)))

please, consider that only you have the TOTAL (possible) combinations of letters & numbers

as I said...
"please, revise and adapt/modify suggested array-formula in [R3] according other "expected" representation (letters & numbers)"
and...
"p.s. don't forget what you just learned in "Hints and Tips for New Posters" ;)"

regards,
hector.

Thanks again Hector. This time just went perfectly with the new
arrangement modified, as follow
=countif(c19:n19,"l")*7.5+sum(if(isnumber(--substitute(lower(c19:n19),"l",5)),--substitute(lower(c19:n19),"l",0)))
I'm expecting get better & set down ideas fluently for these kind of forum. TNX
[Q19] =countif(c19:n19,"l")*7.5+sum(if(isnumber(--substitute(lower(c19:n19),"l",0)),--substitute(lower(c19:n19),"l",0)))

or, from the beginning...

[Q3] =countif(c3:n3,"l")*7.5+sum(if(isnumber(--substitute(lower(c3:n3),"l",0)),--substitute(lower(c3:n3),"l",0)))

please, consider that only you have the TOTAL (possible) combinations of letters & numbers

as I said...
"please, revise and adapt/modify suggested array-formula in [R3] according other "expected" representation (letters & numbers)"
and...
"p.s. don't forget what you just learned in "Hints and Tips for New Posters" ;)"

regards,
hector.

hi, !

... This time just went perfectly with the new arrangement modified, as follow
=countif(c19:n19,"l")*7.5+sum(if(isnumber(--substitute(lower(c19:n19),"l",5)),--substitute(lower(c19:n19),"l",0)))
I'm expecting get better & set down ideas fluently for these kind of forum. TNX

i don't get why you are validating "isnumber(substitute(...,5))"
and (at the end) formula is using a different "substitute(...,0)"

but it's up to you (you are the man in the analysis stuff) :D
and... thanks for the feed-back ;)

regards,
hector.

__ OP __
[Q19] =countif(c19:n19,"l")*7.5+sum(if(isnumber(--substitute(lower(c19:n19),"l",0)),--substitute(lower(c19:n19),"l",0)))

or, from the beginning...

[Q3] =countif(c3:n3,"l")*7.5+sum(if(isnumber(--substitute(lower(c3:n3),"l",0)),--substitute(lower(c3:n3),"l",0)))

please, consider that only you have the TOTAL (possible) combinations of letters & numbers

as I said...
"please, revise and adapt/modify suggested array-formula in [R3] according other "expected" representation (letters & numbers)"
and...
"p.s. don't forget what you just learned in "Hints and Tips for New Posters" ;)"

regards,
hector.