Hey all!

I'm trying to pull out data from an extract that contains employees with their start and finish times worked and split the hours they've worked across 1,2 or 3 different shift times.

My problem is the extract doesn't give me a date, just an AM/PM time for both start & finish so there are people working over midnight and midday.

Second problem being I have 3 shifts (0600-1400, 1400-2200, 2200-0600) and almost everyone will work across more than one shift time.

I have been playing around with formulas for literal hours - my focus was getting the overnight shift nailed and I thought I did last night but then looked at it again this morning and realized I hadn't factored in the fact that someone might start during that shift but their finish time will fall into the other shift.

Help me! Before I spend another 4hrs frying my brain on this!

Also - I need each shifts hours from each employee to fall into a different column ie; someone worked 4hrs across night and 3hrs across day then I'd need to see 4.0 in Column B and 3.0 in Column C etc.

The formula I am using for overnight workers calc (which is not up to scratch it's just where I've given up) is..

=IF(OR(ISNUMBER(SEARCH("*PM*",G13)),ISNUMBER(SEARCH("*NIGHT*",G13)),ISNUMBER(SEARCH("*ARVO*",G13)),ISNUMBER(SEARCH("*ARVO/NIGHT",G13))),IF(E13>4,E13-0.2,IF(AND(E13>4,E13<6),E13-0.3,IF(AND(E13>6,E13<9),E13-0.8,IF(E13>9,E13-1.1,E13)))),"0")

Again - heeeeelllppp

TIA

I'm trying to pull out data from an extract that contains employees with their start and finish times worked and split the hours they've worked across 1,2 or 3 different shift times.

My problem is the extract doesn't give me a date, just an AM/PM time for both start & finish so there are people working over midnight and midday.

Second problem being I have 3 shifts (0600-1400, 1400-2200, 2200-0600) and almost everyone will work across more than one shift time.

I have been playing around with formulas for literal hours - my focus was getting the overnight shift nailed and I thought I did last night but then looked at it again this morning and realized I hadn't factored in the fact that someone might start during that shift but their finish time will fall into the other shift.

Help me! Before I spend another 4hrs frying my brain on this!

Also - I need each shifts hours from each employee to fall into a different column ie; someone worked 4hrs across night and 3hrs across day then I'd need to see 4.0 in Column B and 3.0 in Column C etc.

The formula I am using for overnight workers calc (which is not up to scratch it's just where I've given up) is..

=IF(OR(ISNUMBER(SEARCH("*PM*",G13)),ISNUMBER(SEARCH("*NIGHT*",G13)),ISNUMBER(SEARCH("*ARVO*",G13)),ISNUMBER(SEARCH("*ARVO/NIGHT",G13))),IF(E13>4,E13-0.2,IF(AND(E13>4,E13<6),E13-0.3,IF(AND(E13>6,E13<9),E13-0.8,IF(E13>9,E13-1.1,E13)))),"0")

Again - heeeeelllppp

TIA