time and hours?

  • Thread starter Thread starter cassie
  • Start date Start date
C

cassie

Hello~
i am using microsoft excel to help make out schedules.
i want to be able to put in my times and have excel
figure the hours for me. for instance if i have an
employee who is working 10-5 for five days a week, i want
excel to calculate the hours for that week. does anybody
know how to do that
 
Hi Cassie!

If you put your hours of clocking on in columns A and B

In column C put:

=B1-A1+(B1<A1)
Format hh:mm

The sum of seven of these would be

=SUM(C1:C7)
Format [hh]:mm

The +(B1<A1) bit is used to cover the case where you clock off after
Midnight. Since time is recorded as a decimal part of a day, going
over Midnight means that you have to add 1. If you go over Midnight
the clock off time will be less that the clock on time and (B1<A1)
returns TRUE which within a maths expression is coerced to 0. In
"Normal time" cases (B1<A1) will return FALSE which is coerced to 0.

See:
Chip Pearson:
http://www.cpearson.com/excel/overtime.htm
Employee TimeSheet download
http://j-walk.com/ss/excel/files/general.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hello Chip,
thanks for writting back. but i am still a little
confused. ok i never work over midnight. so the +1
thing shouldn't be a problem. now lets say i put 10:00
am (clock in time) in column A2 and 4:00 pm (clock out
time) in column B2, then for the next day 10:00 am in C2
and 4:00 pm in D2. is this the right way, and if it is
what is the exact formula i would use if this would go on
for 7 days. sorry, i am not very knowledgable when it
comes to using excel,
thanks,
cassie
-----Original Message-----
Hi Cassie!

If you put your hours of clocking on in columns A and B

In column C put:

=B1-A1+(B1<A1)
Format hh:mm

The sum of seven of these would be

=SUM(C1:C7)
Format [hh]:mm

The +(B1<A1) bit is used to cover the case where you clock off after
Midnight. Since time is recorded as a decimal part of a day, going
over Midnight means that you have to add 1. If you go over Midnight
the clock off time will be less that the clock on time and (B1<A1)
returns TRUE which within a maths expression is coerced to 0. In
"Normal time" cases (B1<A1) will return FALSE which is coerced to 0.

See:
Chip Pearson:
http://www.cpearson.com/excel/overtime.htm
Employee TimeSheet download
http://j-walk.com/ss/excel/files/general.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
cassie said:
Hello~
i am using microsoft excel to help make out schedules.
i want to be able to put in my times and have excel
figure the hours for me. for instance if i have an
employee who is working 10-5 for five days a week, i want
excel to calculate the hours for that week. does anybody
know how to do that
Click to expand...


.
Click to expand...
 
=sum(B2,D2,F2,H2,J2,L2,N2)-sum(A2,C2,E2,G2,I2,K2,M2)

format as Time or multiply the result by 24 to get hours

=(sum(B2,D2,F2,H2,J2,L2,N2)-sum(A2,C2,E2,G2,I2,K2,M2))*24

--
Regards,
Tom Ogilvy

cassie said:
Hello Chip,
thanks for writting back. but i am still a little
confused. ok i never work over midnight. so the +1
thing shouldn't be a problem. now lets say i put 10:00
am (clock in time) in column A2 and 4:00 pm (clock out
time) in column B2, then for the next day 10:00 am in C2
and 4:00 pm in D2. is this the right way, and if it is
what is the exact formula i would use if this would go on
for 7 days. sorry, i am not very knowledgable when it
comes to using excel,
thanks,
cassie
-----Original Message-----
Hi Cassie!

If you put your hours of clocking on in columns A and B

In column C put:

=B1-A1+(B1<A1)
Format hh:mm

The sum of seven of these would be

=SUM(C1:C7)
Format [hh]:mm

The +(B1<A1) bit is used to cover the case where you clock off after
Midnight. Since time is recorded as a decimal part of a day, going
over Midnight means that you have to add 1. If you go over Midnight
the clock off time will be less that the clock on time and (B1<A1)
returns TRUE which within a maths expression is coerced to 0. In
"Normal time" cases (B1<A1) will return FALSE which is coerced to 0.

See:
Chip Pearson:
http://www.cpearson.com/excel/overtime.htm
Employee TimeSheet download
http://j-walk.com/ss/excel/files/general.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
cassie said:
Hello~
i am using microsoft excel to help make out schedules.
i want to be able to put in my times and have excel
figure the hours for me. for instance if i have an
employee who is working 10-5 for five days a week, i want
excel to calculate the hours for that week. does anybody
know how to do that
Click to expand...


.
Click to expand...
Click to expand...
 
hello~
thanks again for writting back. the problem i am having
is. i hit =sum(B2,D2,ETC...) i get #NAME, #VALUE, or i
get #####. what am i doing wrong?
-----Original Message-----
=sum(B2,D2,F2,H2,J2,L2,N2)-sum(A2,C2,E2,G2,I2,K2,M2)

format as Time or multiply the result by 24 to get hours

=(sum(B2,D2,F2,H2,J2,L2,N2)-sum(A2,C2,E2,G2,I2,K2,M2))*24

--
Regards,
Tom Ogilvy

cassie said:
Hello Chip,
thanks for writting back. but i am still a little
confused. ok i never work over midnight. so the +1
thing shouldn't be a problem. now lets say i put 10:00
am (clock in time) in column A2 and 4:00 pm (clock out
time) in column B2, then for the next day 10:00 am in C2
and 4:00 pm in D2. is this the right way, and if it is
what is the exact formula i would use if this would go on
for 7 days. sorry, i am not very knowledgable when it
comes to using excel,
thanks,
cassie
-----Original Message-----
Hi Cassie!

If you put your hours of clocking on in columns A and B

In column C put:

=B1-A1+(B1<A1)
Format hh:mm

The sum of seven of these would be

=SUM(C1:C7)
Format [hh]:mm

The +(B1<A1) bit is used to cover the case where you clock off after
Midnight. Since time is recorded as a decimal part of
Click to expand...
a
day, going
over Midnight means that you have to add 1. If you go over Midnight
the clock off time will be less that the clock on time and (B1<A1)
returns TRUE which within a maths expression is
Click to expand...
coerced
to 0. In
"Normal time" cases (B1<A1) will return FALSE which is coerced to 0.

See:
Chip Pearson:
http://www.cpearson.com/excel/overtime.htm
Employee TimeSheet download
http://j-walk.com/ss/excel/files/general.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Hello~
i am using microsoft excel to help make out schedules.
i want to be able to put in my times and have excel
figure the hours for me. for instance if i have an
employee who is working 10-5 for five days a week, i want
excel to calculate the hours for that week. does anybody
know how to do that


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Cassie!

In C2 use:

=B2-A2+(B2<A2)
Format hh:mm
This should give you the elapsed time.
This formula can be copied down for the seven days.

Scream now if you don't get the right answer.

If you don't scream:

Having entered the formula. Point to the bottom right corner until you
see the + and then double click the plus and it will copy down as long
as you have entries on the left.

You now have elapsed times for each of seven days in C2:C8
In C9 put:
=SUM(C2:C8)
Format using Custom Format [hh]:mm
Or
Use:
=SUM(C2:C8)*24
Format General



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
cassie said:
Hello Chip,
thanks for writting back. but i am still a little
confused. ok i never work over midnight. so the +1
thing shouldn't be a problem. now lets say i put 10:00
am (clock in time) in column A2 and 4:00 pm (clock out
time) in column B2, then for the next day 10:00 am in C2
and 4:00 pm in D2. is this the right way, and if it is
what is the exact formula i would use if this would go on
for 7 days. sorry, i am not very knowledgable when it
comes to using excel,
thanks,
cassie
-----Original Message-----
Hi Cassie!

If you put your hours of clocking on in columns A and B

In column C put:

=B1-A1+(B1<A1)
Format hh:mm

The sum of seven of these would be

=SUM(C1:C7)
Format [hh]:mm

The +(B1<A1) bit is used to cover the case where you clock off after
Midnight. Since time is recorded as a decimal part of a day, going
over Midnight means that you have to add 1. If you go over Midnight
the clock off time will be less that the clock on time and (B1<A1)
returns TRUE which within a maths expression is coerced to 0. In
"Normal time" cases (B1<A1) will return FALSE which is coerced to 0.

See:
Chip Pearson:
http://www.cpearson.com/excel/overtime.htm
Employee TimeSheet download
http://j-walk.com/ss/excel/files/general.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
cassie said:
Hello~
i am using microsoft excel to help make out schedules.
i want to be able to put in my times and have excel
figure the hours for me. for instance if i have an
employee who is working 10-5 for five days a week, i want
excel to calculate the hours for that week. does anybody
know how to do that
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top