Back to Forms in Excel VBA archive index

Back to archive home

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. . .

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

for each time do a calculation

=hour(cell ref) + (1/60)*minute(cell ref)

=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

or, more directly

if 6:30am is in cell a2 and 3:30pm is in a3:

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

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

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.

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.