How to sum specific timevalue in row?

**k9aland** · 07-26-2012, 12:55 PM

Hello everyone!
First two hours in this forum and been working on this isue several days and are stuck!

I have build an work planing for my department and are very satifyed with the data it gives out regarding calculation of overal time for each of the employies and also for the data that i have extracted from this, but now to next stage. As you see I have two rows for each employ where the upper one is for shift that continues on next day and the lower one is for shift that are inside that actual day.
This exampe give one of eight work templates, the formula have to cover both ful rows.

I would like to sum the time between 18:00 and 21:00, those hours give 15 % compensation
I would like to sum the time between 21:00 to max 12:00 fowowing day, those hours give 40% compensation
I would like to sum the time between Saturday 06:00 to Saturday 18:00, those give 25% in compensation
I would like to sum the time between Saturday 18:00 to Sunday 24:00, those give 100 % in compensation.

One extra, there are holidays (probably different holiday list) that would be nice to incoporate, those holidays are diferent from year to year, for exaple Christmas eve.

Hopefully someone could shed some light on how to extract those hours, I am most pleased!
Magnus

**k9aland** · 07-31-2012, 02:43 PM

BUMP????!!! Am I allowed to bump!

Is this an inposible equation, this layout of sheet?
If there is not an posibility to calculate the requested info, do anyone have good suggestion how I should make a layout of emploies working times over three weeks for about 50 emploies, within that layout there are much better posibility?
I am quite satisfied with the data i get out of this sheet and made a great amout of work already!
anyone? Suggestions!
PS! I have put in the rest of calculations for the sheet, so you can see that I have made some good work on it!
Magnus

**Cutter** · 07-31-2012, 04:31 PM

Am I allowed to bump!

Yes. The following is from the suggestions listed below the forum rules (viewed by clicking Forum Rules button @ top of page):

If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

**k9aland** · 08-23-2012, 03:07 PM

As you see I have developed this workschedule and how to extract value from "worker" rows.
In this attacement you see that have worked out how to extract value between 18:00 to 21:00.
Also value between 21:00 and 24:00.

This is the intended formula for time between 00:00 to max 12:00

=OM(B9>=$O$8;0;(B9>D9)*MEDIAN(0;D9-0/24;12/24)+MAX(0;MIN(12/24;D9+(B9>D9))-MAX(0/24;B9)))

. $O$8 has value 06:00.

I have this formula to work in another worksheet for Saturday 06:00 to 18:00

=IF(WEEKDAY(A9;2)=6;IF(B9>$P$6;"";IF(AND(B9>$P$5;D9<$P$8);D9-B9;IF(AND(B9<$P$7;D9>$P$7);$P$7-B9;IF(AND(B9<$P$5;D9>$P$5);D9-$P$5;""))));"")

Value for P5=06:00, P6=17:00, P7=18:00 and P8=19:00.

I have this formula to work in another worksheet for time between saturday 18:00 to sunday 24:00

=OM(VECKODAG(A9;2)=7;E9;OM(OCH(VECKODAG(A9;2)=6;OCH(D9>$P$7;B9<$P$7));D9-$P$7;OM(OCH(B9>$P$7;VECKODAG(A9;2)=6);D9-B9;"")))

Value for P7=18:00
Value for E9=(D9-B9)

Absolutly not the prettiest one, with many columns to hind when doing like this, any good ideas?

But the main question beside the layout, how to inplement in the formulas "holiday" that are not Saturday and Sunday. I think it should bee with some table of actuale holiday dates, where the formula gets the data to look to for example 24.12.2012 (monday) that is should bee considered as an sunday!

I am pretty close now, but absoloutly not the prettiest one, but nice function to hide columns, he he!

Magnus