separate night from day hours

**manousakis** · 03-12-2013, 04:44 PM

hello

i am trying for days now to find a solution ,but no luck (excel 2007).

pls help

i want to separate night hours 22:00-6:00
fom day hours 6:00-22:00
in my work plan.

i am using all kind of shifts for example

8:00-23:00 = 14 day hours 1 night hour
21:00-7:00= 2 day hours 8 night hours.
16:00-2:00=6 day hours 2 night hours

any help pls!!!

**Melvinrobb** · 03-12-2013, 05:48 PM

Can you post a sample workbook of exactly how you want your desired solution to look?

**manousakis** · 03-13-2013, 02:53 AM

Thanks for the reply,

there is a sample, the reason i want to separate is that the employes get payd 25% for night hours [22:00-6:00] so i have to separate day from night hours to make the calculation.

Thanks

**Fotis1991** · 03-13-2013, 03:49 AM

Hi-Καλημέρα Ελλάδα.

In C8, put this formula for day hours.

=(C5>D5)*MEDIAN(0;D5-1/4;2/3)+MAX(0;MIN(11/12;D5+(C5>D5))-MAX(1/4;C5))

In C9 put this one. This one subtract the day hours from the total hours. So the result is the night hours.

=D5-C5+(C5>D5)-C8

Copy across.

**manousakis** · 03-13-2013, 06:51 AM

ΚΑΛΗΜΕΡΑ !!!

Thanks for the reply,

the first formula with a little improve (i had to deal with the off in a cell) works fine =if(and(isnumber(c5);isnumber(d5));(c5>d5)*median(0;d5-1/4;2/3)+max(0;min(11/12;d5+(c5>d5))-max(1/4;c5)))*24

but the second does not seem to work!

Ι post again the sample workbook to confirmation of the above.

any further help would be appriciate

Thanks in advance

**Fotis1991** · 03-13-2013, 09:33 AM

Καλησπέρα.

I don't understand which could be the problem for the second formula.

Pls, take a look to the example.

Από που είσαι πατριώτη; Πως σε λένε;