MrExcel Consulting
Your One Stop for Excel Tips & Solutions

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?!!!!!!

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

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

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

2 Answers for you


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

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

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


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