Any ideas how to calculate timesheet?

Hi, i have a table with lost of information and have to calculate hours how mush each shift is doing.
For example RDO - Rest Day Off.
I need this:
to know how many each shift on that day is working hors.
Shifts are: Earlies 06-14, Afters 14-22, nights 22-06.
Coleagues can be doing overtime or changing hours to such situation: 04to12.
I need on that day to calculate how many hours each shift is doing. File is big, for a whole year and lots of names
it would be easier if just i would be using this file, but a problem is that format in each cell is this way:
04to14
04to08 and so on.
for an example i will attach example.
To explain: if coleague is working: 06to14 - Earlies 8hrs
if: 04to12: Nights 2hrs, Earlies 6hrs and so on.

Any ideas?

hi, thank you for reply.

I know that there is text there in work book, and there are few cells with H - Holidays, L - Lieu and so on....
I can manage this by creating lots of hidden sheets, but then file is becoming... big... nearly 130MB
simply by creating hidden sheets which ones extracts hours like that: if(isnumber(left(cell,2)*1),left(cell,2)*1,"")
i have to multiply by 1 then excel understands it as number.
Problem is becoming when rows: lots of dates and lines: nearly thousand of colleagues
Trick is that this information is filled my managers, and cells are locked to select just from specific range, otherwise it will be a mes...
any ideas? visual basic? now looking for such information but do not think it will help a lot

I think this does what you need:
timesheet_template_cy.xlsx

Hi cyiangou, thank you for making this file, looks good, but the first line is not calculating correct values:
it gives just 2 hours instead of 6, because colleague was working 22-04.
Also is it possible to make formula that it would be just in one cell?
like new sheet:
A1: will calculate night hours
A2: Earlies
A3: afters
B1, B2 and B3 would calculate another row?
I will have to make then another formulas to get some information according those numbers.

but the first line is not calculating correct values: it gives just 2 hours instead of 6, because colleague was working 22-04.

I need this: to know how many each shift on that day is working hours.

The two hours means (correctly, according to your requirement), that only 2 hours were spent on night shift on that day. Hours from 00:00 to 04:00 belong to the next day.

Also is it possible to make formula that it would be just in one cell?

I deliberately didn't combine everything to make my workings clear, and especially to show how to fit time spans into time bins (ie. dealing with night shift splits across midnight). You now have all the components, so you can re-assemble it as you please.

OK, thank you for this, i will do my best to figure it out.

Regards
Tomas

ok, have an idea, but need some help
how to make sumproduct and iferror work together
is there any chance to sumproduct (if first 2 numbers ar less then 6) in such row?

i am tryng to figure out like this:
=SUMPRODUCT((IFERROR(MID(A:A,3,2)="to",0)*1)*(LEFT(A:A,2)*1<6)*(LEFT(A:A,2)*1)) --i am trying to make this formula work.
SUMPRODUCT((MID(A:A,3,2)="to")*(LEFT(A:A,2)*1<6)*(LEFT(A:A,2)*1)) ---this works till i get blank cell or anythng else that does not have "to", such like: RDO

21to23
03to15
07to12
02to15
rdo
04to12
04to15
07to12
07to14
H