I am trying to formulate a payroll spreadsheet which will calculate hours and
subtract the number of minutes taken for lunch.
On the current spreadsheet the employee start time and end time are entered
(12 hour format) and number of minutes in separate cell (ie 0:30)
I'm also calculating a cumulative total of hours to help control overtime.

I've had two issues arise.
# 1 once the calculated cumulative number of hours goes over 24 it starts
over again
# 2 I can't get the # minutes into the formula to be subtracted from.

My current spreadsheet Shows the start time in column c, end time in column
d, and number of minutes for lunch in column e (using time format 12:00)
calculated daily hours in column f and cumulative number hours during week in
column g.
I understand that it would probably be easier to calculate the time using 24
hr clock instead of straight 12 hour clock. The formula currently being used
is (D2-C2)-e2

Sorry for being so long on the question but I can't seem to find any answer
or template that would help on this.

--
dsimpkins

24 hour vice 12 hours is just a matter of formatting. The underlying value
is the same. Dates (and time) are stored as the elapsed number of days (and
time is fractions of a day) from a base date. If you only enter a time
value, it is stored as a number less than one from the base date.

You control the display by formatting. To keep your cumulative time from
rolling over, select the cell and apply a custom number format
(format=Cell=Number Tab, choose custom).

[hh]:mm

Or go to the time entries and select one that shows time values greater than
24.

there is no reason your formula shouldn't work, so I suspect it is a
formatting problem as well except if the user puts in

30:00 for 30 minutes, they actually are entering 30 hours. 0:30 would be
30 minutes. If they just enter the number 30, then it is might even be
interpreted as 30 days.

http://www.cpearson.com/excel/datetime.htm#SerialDates

--
Regards,
Tom Ogilvy




"dsimpkins" wrote:

I am trying to formulate a payroll spreadsheet which will calculate hours and
subtract the number of minutes taken for lunch.
On the current spreadsheet the employee start time and end time are entered
(12 hour format) and number of minutes in separate cell (ie 0:30)
I'm also calculating a cumulative total of hours to help control overtime.

I've had two issues arise.
# 1 once the calculated cumulative number of hours goes over 24 it starts
over again
# 2 I can't get the # minutes into the formula to be subtracted from.

My current spreadsheet Shows the start time in column c, end time in column
d, and number of minutes for lunch in column e (using time format 12:00)
calculated daily hours in column f and cumulative number hours during week in
column g.
I understand that it would probably be easier to calculate the time using 24
hr clock instead of straight 12 hour clock. The formula currently being used
is (D2-C2)-e2

Sorry for being so long on the question but I can't seem to find any answer
or template that would help on this.

--
dsimpkins