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