Index Values

[Afarag]

Hello,

please I need your support at an inquiry which somehow complicated,
en recap, as shown, this is a shift schedule with breaks for employees, e.g: the first agent

Red color is the 1st Break start: 11:15, end 11:30 >> Duration "0:15:00"
Yellow color is the Lunch Break start: 12:30, end: 13:00 >> Duration "0:30:00"
2nd Red color is the last Break start: 15:00, end 15:15 >> Duration "0:15:00"

Note: the colors represent the scheduled breaks, but the filling values are the actual which agent applied per minutes.

1st Break start: 11:16, end 11:30
Lunch Break start: 12:32, end: 13:02
2nd Red color is the last Break start: 15:01, end 15:15

my request is how can I export that actual time not manually, I see there is something must figure out, "the values didn't match permanently the colors" e.g: the lunch for 5th agent, as he scheduled 12:30 till 13:00, but the action was taken at 13:13 till 13:43.
sometimes the value comes after the colored Quarter and sometime before.

Gratefully

 

[vletm]

Could You fill wanted 'exported' output to another sheet?

 

[Afarag]

it's not a must for me, but if you will fill the output to another worksheet, I need the names at column A beside the output

 

[vletm]

You have sample inputs there now, okay?
As I tried ..
Could YOU fill wanted 'exported' output to another sheet?
That I could see clear vision or Your wanted 'exported' output!

 

[Afarag]

the exported data is just the names and their such Breaks start and end

 

[vletm]

Questions?
1) You wrote that:
the filling values are the actual which agent applied per minutes.
means that not work in that 15minutes ... like:
[O4] 14,25 = 14minutes (why there are many case some decimals?)
[T4] 12,66.. [U4] 15 [V4] 2,2166...
for me, possible minute values are from 0 to 15.
2) Why do You try to solve 'breaks'?
Do they get something extra from those?
Why not try to solve 'working times'?
... something like... colorful cells *15minutes - sum of 'breaks'

 

[Afarag]

Hello Vletm,
|: we can overlook the minutes' fractions.
||: About solving breaks, every agent must take his breaks as scheduled, he must not Leads or lags his break, as I have the datarow of Scheduled breaks for every agent and in getting the Actual, i'll subtract the Actual from the scheduled to get the variance.

hope if this would help

 

[vletm]

Press 'breaks' button
and
check breaks.
I overlook the minutes' fractions ... (14,25 => 14).

 

[Afarag]

it works like a charm, thanks a lot vletm

 

[Afarag]

Hi @vletm

sorry to back, your code does the function well but I'd noticed that it starts counting the number at the beginning of the colored cell then its next, but what if the previous colored cell have values e.g

at row8 >> 1st Break, it should start from 11:14 till 11:29, because there is one minute at "quarter of 11"

[/url]hebergeur gratuit[/IMG]

thanks a lot,

 

[vletm]

You wrote that OVERLOOK the minutes' fractions!
Cell [N8] has value 0,5 ... then it is 0 ... as You wanted!
It's same case as with Cell [L7]. The value is 0,583333... it's same as 0!

I formatted few sample cells for You.
If would like to have some break before 11:15
then the value before 11:15 should be >= 1!
Ideas ... Questions?

 

[Afarag]

yes vletm, I got your idea, i made a little modification at data range, then try to apply the code, but an error occurred, i need to fill the output at range ("BX5:CC")

thanks a lot for your brilliant code, it saves a lot of wasted time anf effort for me

 

[vletm]

YES, YES ... I like sentence 'i made a little modification at data range, ...', sorry!
I noticed that! ... just a little ...
I think that You have used to play only with formulas, hmm?
You add few columns, You add 'few' same kind formulas ... and now no work!
I tried to 'save' something.
If add column then it normally should modify code.
Why there are over 16k formulas? ... heavy calculations!
... after You press 'Fill formula' then You could save those as values?
.... and where do those results of formulas come? (0..15)
There are still (too) many formulas to make this slow ... just my opinion.
I had to set manually calculation because ... it takes too long time!
and
now same time as solving marked breaks,
it will solve also scheduled breaks (and just only those two colors!).
> Ideas ... questions?

 

[Afarag]

Hello @vletm

please I need your support at the sheet, as you remember the project based on 2 short Breaks with Red color and Lunch with yellow color, but I added an Extra Break with pistage color which I need to get its scheduled frame time and actual duration as used for the other Breaks. But there is some points we need to consider: that Extra Break will not always 15 minutes, sometimes it will be 30 minutes, its orders (position) isn't always fixed, may be after 1st Break or after lunch or after last break

the full workbook attached because its size exceeded 1 Mega,
https://mega.nz/#!GYtmABTL!KMaVMz6fnO3chb-BfrG5C5DoCEKBzVyZNNbU0UFBT7c

Gratefully,

 

[vletm]

hmm ... I modify that file
... but how do want to get it?
Send message with email-address?