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

 

[elynoy]

Hello again.

Anyone can help me with this?

Best regards,
eLy

 

[Gerald Higgins]

Hi, I remember this one from first time round, I didn't understand it then and I still don't

Please provide a small sample of your data - say a dozen lines or so - that include several different types of scenario that you might have, and describe clearly what the results should be, and why.

What do your individual columns refer to ?
Some header titles might be useful.

What do you mean when you say

the letter A should be able to choose between those 2 shifts

What do you mean when you say

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

 

[elynoy]

thanks for the reply. I uploaded a sample file with examples in hope that it's well explained on what it does and how i need it to work as intended and not as a workaround like explained in the sample file.

file: https://ufile.io/kcztfo32

Best regards,
eLy

 

[Gerald Higgins]

OK well personally I don't want to follow that link, sorry.
Maybe others on the board will.
Good luck !

 

[elynoy]

is there a place i can store a sample file so you can check?

eLy

 

[Gerald Higgins]

Personally I'm not willing to look at a sample file, though others on this board might be.

If you can post a sample of your data directly into a post in this thread, I'll look at it.

 

[elynoy]

I'll try to detail it.
I have this formula to get the night shifts hours:

=SE(X19="new";(($E19>$F19)*MED(0;$F19-$L$2;MED($L$2;$K$2))+MÁXIMO(0;MÍNIMO($K$2;$F19+($E19>$F19))-MÁXIMO(1/4;$E19)))-((F7-E7)+(D7-C7));(($E19>$F19)*MED(0;$F19-$L$12;MED($L$12;$K$12))+MÁXIMO(0;MÍNIMO($K$12;$F19+($E19>$F19))-MÁXIMO(1/4;$E19)))-((F7-E7)+(D7-C7)))

[TABLE="width: 472"]
<tbody>[TR]
[TD]night[/TD]
[TD]day[/TD]
[TD]Total[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 5"]Worker letter[/TD]
[/TR]
[TR]
[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]a[/TD]
[TD]a[/TD]
[/TR]
</tbody>[/TABLE]

that formula is in the day shift. then I have

=D19-C19

to get the night hours. in that example it's 9 hours worked from 20:00 to 08:00. night shift is from 21:00 to 06:00.

where you see worker letter there's A and B in there. letter A night shift is from 20:00 to 06:00 and letter B night shift is from 21:00 to 06:00. Both of the letters are in the same row so it will "lock" that line to a night shift. either it's the A or B. I need the ability to put the letters in the same row but make it count the letters with each shift.
So, in that example there's A and B. works 45 hours as night shift for the amount of time it appears on that row.
The way I found to work around this is to set each letter to a different shift and different row, something like this:

[TABLE="width: 608"]
<tbody>[TR]
[TD]night[/TD]
[TD]day[/TD]
[TD]Total[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 5"]Worker letter[/TD]
[TD][/TD]
[TD]Night hours for letter A[/TD]
[/TR]
[TR]
[TD]11:00[/TD]
[TD]01:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD][/TD]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]09:00[/TD]
[TD]03:00[/TD]
[TD]12:00[/TD]
[TD]20:00[/TD]
[TD]08:00[/TD]
[TD][/TD]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[TD]b[/TD]
[TD][/TD]
[TD]18
night hours for letter B[/TD]
[/TR]
</tbody>[/TABLE]

if this is not clear let me know.

eLy

 

[Gerald Higgins]

Why exactly is the answer 33 for "a" and 18 for "b" ?

Is 18 because it's 2 x 9 ?

It sounds as if POSSIBLY something like COUNTIF or SUMIF might help you, but I still don't really understand what you're trying to do.

Don't worry too much about explaining the real world application of what you're trying to do, but please try and be super clear about what data you have, and exactly what you want to do wtih it.

 

[elynoy]

Yes, it's the amount of hours worked that shift , for letter A, 3 nights from 20:00 to 08:00 with the correspondent "night shift" from 20:00 to 06:00.
thats why the "night" for letter A has 11 for those 3 nights and B has 9 for 2 nights.

Like I said. I'd like to be able to keep the letters in the same row but each letter has his own "night shift". Letter A is 20:00 to 06:00 and letter B is 21:00 to 07:00.
That example has them separated as a workaround wich isnt practical for me.

best regards,
eLY