Grand total time between two total cells

[Guest]

I have a payroll worksheet which houses an employee's start time, lunch
start/stop and end time. It also notes any Flex-time for an employee. In
these cells I enter the time for each (in military). Under the first column,
start...end I have a total. Under Flex time...start/end...I have a total.
To get the total hours worked I am trying to sum the regular total and flex
total but the sum is incorrect.

Any suggestions?

START TIME 8:30
LUNCH START 11:00
LUNCH STOP 11:30
END TIME 14:30
Total Time 5.50

Flex-time (2 hours minimum/max)
Start 15:00
End 17:00
Total 2:00

Total Hours worked 14:00 <<<--- This is what is not adding correctly.

 

[Peo Sjoblom]

I get 7:30

=D1-A1-(C1-B1)

where D1 is end, A1 is start, C1 is lunch end and B1 lunch start, that will
return 05:30 formatted as hh:mm then

=F1-E1

where F1 is flex end and E1 flex start, that gave me 02:00

then totaled gave me 07:30



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

=SUM((C12-C11)*24,(C10-C9)*24)

This is the formula I am using to get the total for Start/End/Lunch
Start/Lunch End
then
=SUM(B19-B18) for total Flex Start/End.

When I add the total for each I get 14:00. That formula is the standard sum
=SUM(B20+B13).

 

[mphell0]

You don't need all the SUMs. Try this:

For Actual Hours:
=((C12-C11)+(C10-C9))*24

For Flex Hours:
=(B19-B18)*24

For Total:
=B20+B13

Format All of the totals as General. Your answer will be 7.5.


You could also eliminate all of the "*24"s and format everything as
time and
your answer will be 7:30

 

[Peo Sjoblom]

Change the first formula to

=SUM(C12-C11,C10-C9)

also no need to use SUM for the second formula if you only use 2 values and
subtraction

=B19-B18

will suffice, then for the final formula you can use *24

=SUM(B20,B13)*24

(no need for internal plus signs within a SUM formula)

format cell as general (not time) and you will get

7.5 hours there you can multiply it with a payrate if needed, if not needed
omit the
*24 and format as time will give you 7:30

what you did was to convert the first calculation to decimal hours, then add
it to time formatted flex time which is 134 hours (5.5 is 5 days and 12
hours in Excel time)
and since you are using hh:mm it will only display what is over 133/24 which
is 14:00


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com