Adding shift time in a roster

[ianmb]

I am trying to add the shift hours in a roster. What formula can I do for this>?

The time is on a 24 hr basis and calls are formatted in hh:mm:ss.

A1 contains the shift start and B2 the shift end for a particlar day. This is the same for all the days of the week. When the employee is not working "OFF" is written in the cells.

thanks for the help!!

Ian

[deemo]

Hi Ian, not really clear what you want to do... you'll have to format the cells you will enter the shift hours in in a time format (Ctrl-1, Number - Time). If this doesn't help, upload an example?

[ianmb]

Mon 12th April Tue 13th April Wed 14th April Thurs 15th April Fri 16th April Sat 17th April Sun 18th April

Start End Start End Start End Start End Start End Start End Start End
09:00 15:00 09:00 15:00 09:00 15:00 09:00 15:00 09:00 15:00 OFF OFF OFF OFF
OFF OFF 13:30 19:30 14:00 19:30 12:30 17:30 15:00 18:30 OFF OFF OFF OFF


Basically I want to add the total number of shift hours ie monday for example 15:00 hrs to 17:00 hrs (in seperate cells) for the whole week and get the total number of hours

[deemo]

Sorry ianbm, upload a spreadsheet which should make it clear. I can't make out what you want to do (although others might do!)

[ianmb]

Hi I have attached a sample of the roster. Basically I want to add the total shift times to get the total hours per week worked for each employee

[Dav]

well if the offs were just shaded the formula would be simpler as you would not need the ifs, but if you want the offs in it try the following in cell P4 and format [hh]:mm

Please Login or Register  to view this content.

Regards

Dav

[ianmb]

thanks dave. i will try it out.

I have no problem leaving the area shaded only. Can you pls give me also the formula for this option to keep it simpler

thanks

[Dav]

As long as I have delete the correct bits from above, you just take start from the finish in each day and add them up, if is formattted as suggested it will display hours and minutes for the week

=C4-B4+E4-D4+G4-F4+I4-H4+K4-J4+M4-L4+O4-N4


Regards

Dav

[ianmb]

thats quite simpler and it works perfectly. thanks a lot for your help