Need help with Excel - Pretty please

richmond

richmond

Messages
1,122
Name
Diane
Edit My Images
Yes
:thinking: :shrug: OK guys and gals I am at my wits end and can't figure out a formula to use in this problem. I know we have a vast range of knowledge on TP and was hoping someone will be able to help. :)

Each week I have to gather the whole team's (20 individuals) working hours and send them (via the intranet) to the personnel dept.

In a typical week a persons hours could be something like this.

Mon 06:30 - 19:30
Tues D/O
Weds D/O
Thurs 06:30 - 19:00
Fri 06:30 - 19:30
Sat 18:30 - 07:30
Sun 14:30 - 08:30

Total hrs worked = 69.5 (minus lunch breaks x 5hrs = 64.5)

At the moment I have a word doc set with names into which I type in the hours and manually add them up. I know I'll always have to type in the start/finish times but is there a formula I can use in excel that adds them for me ?

The main problem I am having is the fact we don't work 5 day or night shifts. Our week is a combination of days and nights. I am totally stumped on this one :bang: I've read so much about formulas my head hurts. :bonk:

If anyone has any ideas, or a link where I can find practical help, I would be eternally grateful.
 
Maybe I'm missing the point, but why wouldn't you format the cells as times, then use a simple subtract (end time minus start time minus one hour (lunch)) to give the working hours in another column?

That would give one row per person per day, which could be then summed?

It seems too easy, so I guess I'm missing the point!
 
One sec, I have a spreadsheet with the right formulas on for working out hours ;)
 
Damn, no I dont....sorry! I must have deleted it. I'll check on a backup disc in the morning :)
The problem I had was the fact that there are 60 minutes in an hour and all calculations were based on the decimal (base 10) system.
I did work out a correct formula for putting in start and finish times plus any breaks and it worked out the correct number of hours.

Will check tomorrow :)
 
Moadib said:
Maybe I'm missing the point, but why wouldn't you format the cells as times, then use a simple subtract (end time minus start time minus one hour (lunch)) to give the working hours in another column?

That would give one row per person per day, which could be then summed?

It seems too easy, so I guess I'm missing the point!
Click to expand...

Thats what I thought but it dosen't work if day shifts and night shifts are worked in the same week. I'm thinking (it hurts :puke: ) I need to use the IF function, but how ?

Marcel, Thanks I'll look forward to your solution.
 
I think I know how to do it but I am just about to go into a meeting, I'll have a look when I get out
 
you seek JonnyReb...he is the all-knowing-oracle of Excel...
 
I have been adding and cross casting hours for the last 5 years, I wish someone had told me it was possible to format excel then - I have only got one more week of it!
 
Once again TP members have come to the rescue (y)

mmcp42, That appears to be just what I'm looking for. (y) I'll try it out on an old timesheet.

MV, I haven't seen that help site before, so thanks for that also. (y)

Kelack, As with all things microsoft there are many ways of acheiving a result. I'd be interested in your solution. (y)

[S4]I love TP[/S4]
 
I would have done it the same as mmcp42.

Ozgrid is quite a useful site. I tend to go on Excel Forum quite a bit when I get stuck with my VBA
 
The Excel Forum is excellent. I've bookmarked it for future reference.

Many many thanks for everyones help. (y) (y)
 
You must log in or register to reply here.
Back
Top