Wage spreadsheet for hours

[onlinejames]

Hi,
I need help with a formula to add the various normal times and night hours between two times
hours may vary a lot so needs to accommodate these differences.
i have looked for formulas that may help without total success.
looking for something easy to follow
normal hrs are between 06:00 and 18:00
night hours are from 18:00 to 06:00

DAY DATE START TIME FINISH TIME DAY EVE/NIGHT
06:00 to 18:00 18:00 to 06:00
MONDAY 30-June-2014 15:00 23:00

TUESDAY 01-July-2014 8:00 17:00

WEDNESDAY 02-July-2014 23:00 8:00

THURSDAY 03-July-2014 10:30 0:00

[Richard Buttrey]

Hello and welcome to the forum.

E4:

Please Login or Register  to view this content.

F4:

Please Login or Register  to view this content.

G4:

Please Login or Register  to view this content.

H4:

Please Login or Register  to view this content.

Then copy down.

[onlinejames]

it works well thank you, except when there are no hours, then it comes up with 12 in F column and ######## in the G column
any ideas ?

[onlinejames]

sorry to be a pain, there is one more little problem,
if a shift starts at 23:00 and goes through to 08:00 in the morning, the 2 hours past 06:00 should show as 2 hrs of day shift- in the formula you gave it shows all as night hours.

there must be a simpler way of working this out , even if there are extra columns to help

[Richard Buttrey]

Ok. I’m away for the weekend but will take a look on Sunday night.

[onlinejames]

Hi Richard, can you have a look at the problem when you can please

[JeteMc]

Try the following:
E4 and down: =IF(OR(C4="",D4="",WEEKDAY(B4,2)>5),"",IF(D4>C4,MIN(D4,"18:00")-MAX(C4,"6:00"),SUM(MAX(0,"18:00"-C4),MAX(0,D4-"6:00"))))
F4: and down: =IF(OR(C4="",D4="",WEEKDAY(B4,2)>5),"",D4+(D4<C4)-C4-E4)
G4 and down: =IF(OR(C4="",D4="",WEEKDAY(B4,2)<>6),"",D4-C4+IF(D4<C4,1,0))
H4 and down: =IF(OR(C4="",D4="",WEEKDAY(B4,2)<>7),"",D4-C4+IF(D4<C4,1,0))
Note that the value in cell D10 will need to be changed from 1/1/1900 12:00 AM to just 12:00 AM (or zero)
Let us know if you have any questions.