Adding Hours in Excel

[Gadgetman]

Is there a way for Excel to add hours worked?

For Example: In a timesheet, say there's three columns (A,B,C)

In column A employees would enter a start time, in column B an end
time. Is there a way for Excel to automatically enter the total hours
worked for the day in column C? If so, what formula would I use and
how would I format the time cells in columns A and B?

See Below for example

A B C
Start Time | End Time | Total

 

[Bob Greenblatt]

Is there a way for Excel to add hours worked?

For Example: In a timesheet, say there's three columns (A,B,C)

In column A employees would enter a start time, in column B an end
time. Is there a way for Excel to automatically enter the total hours
worked for the day in column C? If so, what formula would I use and
how would I format the time cells in columns A and B?

See Below for example

A B C
Start Time | End Time | Total

Sure! Just insure that Excel thinks the values you entered are valid times.
You'll probably have to enter them as "6:00 am" and "4:00 pm", etc. then
just subtract them and multiply the difference by 24. (The subtraction
creates a result in fractions of a day, so multiplying by 24 results in
hours.) So, in column C enter the formula "=(b1-a1)*24"

 

[Gadgetman]

Sure! Just insure that Excel thinks the values you entered are valid times.
You'll probably have to enter them as "6:00 am" and "4:00 pm", etc. then
just subtract them and multiply the difference by 24. (The subtraction
creates a result in fractions of a day, so multiplying by 24 results in
hours.) So, in column C enter the formula "=(b1-a1)*24"

Thanks Bob,
Any suggestions on how to handle it if the shift is a midnight and
stretches from the evening of one day into the next? For instance, if
the start time is 10:00pm and the end time is the following morning at
6:00am? When I used the formula you suggested and enter those times,
it returns a -16 in the total hours. I realize that Excel is assuming
that the hours used are in the same day, but in instances where a
shift stretches from one day into another, it does not return the
correct value. Any suggestions?
Thanks

 

[Bernard Rey]

Gadgetman wrote :

Thanks Bob,
Any suggestions on how to handle it if the shift is a midnight and
stretches from the evening of one day into the next? For instance, if
the start time is 10:00pm and the end time is the following morning at
6:00am? When I used the formula you suggested and enter those times,
it returns a -16 in the total hours. I realize that Excel is assuming
that the hours used are in the same day, but in instances where a
shift stretches from one day into another, it does not return the
correct value. Any suggestions?

From an earlier post by JE MCGimpsey:
=(B1-A1+(B1<A1)) * 24

or:
=MOD(B1-A1,1) * 24

 

[Bob Greenblatt]

Thanks Bob,
Any suggestions on how to handle it if the shift is a midnight and
stretches from the evening of one day into the next? For instance, if
the start time is 10:00pm and the end time is the following morning at
6:00am? When I used the formula you suggested and enter those times,
it returns a -16 in the total hours. I realize that Excel is assuming
that the hours used are in the same day, but in instances where a
shift stretches from one day into another, it does not return the
correct value. Any suggestions?
Thanks

One way is to enter the day along with the time. You can format the cell so
the day does not show. Then the procedure I already posted will work fine.

 

[Jahn]

That works great. Any idea how to calculate overtime (1.5x) if hours worked
are more than 8 and double time (2x) if hours worked are more than 12?

Example
if a1=8:00 AM and b1=9:00 PM

c1 should equal 8.00 for 8 hours straight time
d1 should equal 4.00 for 4 hours overtime and
e1 should equal 1.00 for 1 hour double time.

Thanks

 

[Peo Sjoblom]

One way, in C1

=MIN(MOD(B1-A1,1),"08:00")

in D1

=MOD(B1-A1,1)-(C1+E1)

in E1

=MAX(MOD(B1-A1,1)-"12:00",0)

assume you have the payrate in F1
for normal hours

=C1*24*F1

for first OT period

=D1*24*F1*1.5

for second OT period

=E1*24*12*2

format the last 3 formulas as currency


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



That works great. Any idea how to calculate overtime (1.5x) if hours worked
are more than 8 and double time (2x) if hours worked are more than 12?

Example
if a1=8:00 AM and b1=9:00 PM

c1 should equal 8.00 for 8 hours straight time
d1 should equal 4.00 for 4 hours overtime and
e1 should equal 1.00 for 1 hour double time.

Thanks

 

[Jahn]

Thanks, that works great!

-----Original Message-----
One way, in C1

=MIN(MOD(B1-A1,1),"08:00")

in D1

=MOD(B1-A1,1)-(C1+E1)

in E1

=MAX(MOD(B1-A1,1)-"12:00",0)

assume you have the payrate in F1
for normal hours

=C1*24*F1

for first OT period

=D1*24*F1*1.5

for second OT period

=E1*24*12*2

format the last 3 formulas as currency


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



That works great. Any idea how to calculate overtime (1.5x) if hours worked
are more than 8 and double time (2x) if hours worked are more than 12?

Example
if a1=8:00 AM and b1=9:00 PM

c1 should equal 8.00 for 8 hours straight time
d1 should equal 4.00 for 4 hours overtime and
e1 should equal 1.00 for 1 hour double time.

Thanks
entered are valid
times. "4:00 pm", etc.
then


.

 

[Darlene Kupke]

Is there a way for Excel to add hours worked?

For Example: In a timesheet, say there's three columns (A,B,C)

In column A employees would enter a start time, in column B an end
time. Is there a way for Excel to automatically enter the total hours
worked for the day in column C? If so, what formula would I use and
how would I format the time cells in columns A and B?

See Below for example

A B C
Start Time | End Time | Total

I keep an Excel worksheet for hours. Just use military time for the end
time. We use a timeclock so I just enter the exact amts.

Example

In Lunch out total
8.25 .75 16.5 7.25

Hope this helps

Darlene