Calculating Time

[Guest]

I need to calculate hours to be worked in one cell. This spreadsheet will
caluclate the hours scheduled for the week, then add them up and mulitply by
payrate:

1 Mon(2) tue(3) wed(4)
thurs(5) total hours
A Employee {8am-1pm) (9am-3pm) (5:30pm-11pm) ???

the question mark marks where i need the formula

 

[Guest]

Hi STephen:

Use two cells for a day the start time and the end time.

You can then do calculations on the time as in

B2=mon start
C2=mon finish
tue d2,e2
wed f2,g2
thur h2,i2
total j2=c2-b2+e2-d2+g2-f2+i2-h2
you need to format the cell j2 as custom ans [h]:mm this will put the right
hrs in and not days and hours.
format the cells as time h:mm

time in

 

[Sandy Mann]

Stephen,

If your work hours are like you posted, (ie (9am-3pm) that is not a very
easy format for Excel to use. I would recommend that you have two columns
per day formated as time, (ie hh:mm) and enter your Start and Finish times
like 9:00 and 15:00 or 9:00 AM and 3:00 PM, (but notice the space between
the time and the AM/PM), in each column then you can sum the total for the
person with the formula:

=SUM(C2-B2,E2-D2,G2-F2,I2-H2)

Custom format the Totals cells as [hh]:mm to stop the hours rolling over
into day at 24 hours.

I am assuming that you are working Monday - Friday and no shift crosses
midnight.

Post back if you need further help.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk