Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Calculating Number Of Hours Worked In A Week????? Help!!!!!!

Posted by Alicia on July 17, 2001 12:26 PM
On my Weekly Schedule spreadsheet, I am trying to figure out how to make the last column calculate the number of hours worked in that week, by each employee. The hours entered for each day look like this:
Monday Tuesday Wednesday etc. . .
6:30 AM 6:30 AM 6:30 AM etc. . .
3:00 PM 3:00 PM 3:00 PM etc. . .

How can Excel calculate that this is 42.5 hours for the week and then display that to me in a decimal format? I've tried and tried and can't figure it out! Help me please?!!!!!!


Check out our Excel Resources

Re: Calculating Number Of Hours Worked In A Week????? Help!!!!!!

Posted by Stephanie on July 17, 2001 12:42 PM
for each time do a calculation
=hour(cell ref) + (1/60)*minute(cell ref)

then you can subtract one from the other to get the number of hours between the two times, then add up across the week


Re: Calculating Number Of Hours Worked In A Week????? Help!!!!!!

Posted by Stephanie on July 17, 2001 12:46 PM
or, more directly
if 6:30am is in cell a2 and 3:30pm is in a3:

HOUR(A3-A2)+(1/60)*MINUTE(A3-A2)
will give the hours between the two times


2 Answers for you

Posted by Ian on July 17, 2001 2:15 PM


if you want it to remain time formatted:
these values you gave I've put in A1 to C3

Monday Tuesday Wednesday etc. . .

Put in the next row A4:

=a3-a2 and copy accross to C3 (or the last day, be it fri, sat etc.)

this will give you the difference between them.

you can then format the cells to (this is an eg, you could just put a row field title "Hour Worked) Format_Number_Custom .. in the box put h:mm"hrs"

or you could hide this row.

to add it all up, in the cell you want put:

=sum(a4:c4)

note: this will return 3:00hrs. you need to format the cell to Format_Number_Custom [h]:mm

the [] round the h tells excel to add the h value rather than see it terms of 1 day.

I say 2 answers but it just a way to return it as a decimal (i.e. 15:45 would be 15.75 as a real number)

in the final cell, change the formula to:

=sum(a4:c4)*24

and format as a general number.

hope this work for you.
I work with times all the time so if you need any help just e.mail @ i.macconnell@btinternet.com and i'll see what i can do.

Ian


2 Answers for you typeO..copy across to c4 not c3

Posted by Ian on July 17, 2001 2:17 PM


if you want it to remain time formatted:
these values you gave I've put in A1 to C3

Monday Tuesday Wednesday etc. . .

Put in the next row A4:

=a3-a2 and copy accross to "C4" (or the last day, be it fri, sat etc.)

this will give you the difference between them.

you can then format the cells to (this is an eg, you could just put a row field title "Hour Worked) Format_Number_Custom .. in the box put h:mm"hrs"

or you could hide this row.

to add it all up, in the cell you want put:

=sum(a4:c4)

note: this will return 3:00hrs. you need to format the cell to Format_Number_Custom [h]:mm

the [] round the h tells excel to add the h value rather than see it terms of 1 day.

I say 2 answers but it just a way to return it as a decimal (i.e. 15:45 would be 15.75 as a real number)

in the final cell, change the formula to:

=sum(a4:c4)*24

and format as a general number.

hope this work for you.
I work with times all the time so if you need any help just e.mail @ i.macconnell@btinternet.com and i'll see what i can do.

Ian


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.