force a letter to a certain time

[elynoy]

Hello. The tittle is wierd because I'm not sure how to call it.

I have a sheet that allows me to choose night shift times for some cells. and based on the time I put there it gives me more or less night hours worked

I need to know if I can make, for exemple, be able to choose the time i want for each person in an interval of cells.

[TABLE="width: 590"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Night time shift[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]night[/TD]
[TD]day[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]21:00[/TD]
[TD]06:00[/TD]
[TD][/TD]
[TD]16:00[/TD]
[TD]24:00:00[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]20:00[/TD]
[TD]07:00[/TD]
[TD][/TD]
[TD]16:00[/TD]
[TD]24:00:00[/TD]
[TD]B[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Let me try to explain: the letter A should be able to choose between those 2 shifts and so should be the letter B.
And in the range of cells given let's say A1 to C10, everytime that letter appears it should count the amount of hours for that letter with that specific shift.


best regards,
eLY

 

[Gerald Higgins]

So is this as "simple" as taking the number in the "night" column, and then multiplying that number by the number of "a"s or "b"s ?

If yes, please post a sample of what your data would ideally look like, if you could get it all on one row.

 

[elynoy]

I'm not sure if it's just that since I got that already, but since it multiplys by the value "night" in the example it uses that value for that row, no matter the letter it have. I had it like that but then I noticed that problem since all the letters in that row would all use the same"night shift" and not the one I want for each letter.

eLy

 

[Gerald Higgins]

Sorry, I still don't understand what you want.

Can you please post SEVERAL lines of data - say half a dozen, not just one ?
Make sure that they all contain DIFFERENT data from each other.
Give the results that you want for each line please.

 

[elynoy]

I'll try my best:
[TABLE="width: 879"]
<colgroup><col><col><col><col span="2"><col><col><col span="2"><col span="2"><col><col span="3"><col><col span="5"></colgroup><tbody></tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]night[/TD]
[TD]day[/TD]
[TD]total[/TD]
[TD]in[/TD]
[TD]out[/TD]
[TD]21:00[/TD]
[TD]06:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]09:00[/TD]
[TD]03:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A2=
C2-B2[/TD]
[TD]B2=
(($D2>$E2)*MED(0;$E2-$G$1;MED($G$1;$F$1))+MÁXIMO(0;MÍNIMO($F$1;$E2+($D2>$E2))-MÁXIMO(1/4;$D2)))[/TD]
[TD]C2=
=RESTO(E2-D2;1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Now, based on the example above I have this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]night[/TD]
[TD]day[/TD]
[TD]total[/TD]
[TD]in[/TD]
[TD]out[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Night hours for letter A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]09:00[/TD]
[TD]03:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I2=
(CONTAR.SE(F2:H2;"A")*A2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


But I' need to be like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]night[/TD]
[TD]day[/TD]
[TD]total[/TD]
[TD]in[/TD]
[TD]out[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Night hours for letter A[/TD]
[TD]Night hours for letter B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]09:00[/TD]
[TD]03:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]a[/TD]
[TD]18[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]I2=
(CONTAR.SE(F2:H2;"A")*A2)[/TD]
[TD]J2=
(CONTAR.SE(F2:H2;"B")*A2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


But: I need the letter B to have a different time from that exemple as night shift:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]night[/TD]
[TD]day[/TD]
[TD]total[/TD]
[TD]in[/TD]
[TD]out[/TD]
[TD]20:00[/TD]
[TD]07:00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11:00[/TD]
[TD]01:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A2=
C2-B2[/TD]
[TD]B2=
(($D2>$E2)*MED(0;$E2-$G$1;MED($G$1;$F$1))+MÁXIMO(0;MÍNIMO($F$1;$E2+($D2>$E2))-MÁXIMO(1/4;$D2)))[/TD]
[TD]C2=
=RESTO(E2-D2;1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



So I need it to be like this example. It's in different rows because it's the way found as a workaround the problem, but I need them to be on the same row:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]night[/TD]
[TD]day[/TD]
[TD]total[/TD]
[TD]in[/TD]
[TD]out[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Night hours for letter A[/TD]
[TD]Night hours for letter B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]09:00[/TD]
[TD]03:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]18[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11:00[/TD]
[TD]01:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[TD]I2=
(CONTAR.SE(F2:H2;"A")*A2)[/TD]
[TD]J2=
(CONTAR.SE(F3:H3;"B")*A3)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thats why I say "force to the letter" because letter A has the night shift from 21:00 to 06:00 and letter B has a night shift from 20:00to 07:00.

I hope this helps because if it doesnt, I dont know how to explain better than this and I'll simply keep doing the way I've been doing till now but with it's inconvenient of using too many rows.

Thanks for your help anyways.

Best regards,
eLy