Overtime hours Time sheet. Knight hours over to next day

N

ndrepebx

New Member
Joined
Mar 2, 2011
Messages
2
Hi
I work on a 24hour Rota, and the shifts change and can be at different hours.
But
Hours between 08:00 and 20:00 are Standard ( 1hr = 1 hr )<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Hours between 20:00 and 08:00 are overtime and paid at time and half (1 hr = 1.5 hrs )<o:p></o:p>
Saturday and Sunday all hours are all overtime at 1.5<o:p></o:p>

I would like to know if there is a way of working out the total of hours per shift. but if there are overtime hours I would like to calculate them at 1.5.

Example below, Start work on Monday at 18:00 and finished at 02:00 on Tuesday morning so there are 6hrs x 1.5 and 2hrs x1.
6x1.5 = 9 + 2x1=11hrs.

My formula for Monday below shows the amount of hours worked =8 - 0.5 (wich is the break/lunch) =7.5

Monday formula =24*MOD(D4-C4,1)-E4
Saturday formula =(24*MOD(D9-C9,1)-E9)*1.5


A4-------------B4-----------C4---------D4------E4--------F4<o:p></o:p>
Day-----------Date--------- Start -----Finish --Break-----Hrs Worked
Monday------31/01/11------18:00-----02:00 ----0.5------ 7.5
Saturday-----05/02/11------08:00-----18:00-----0.5------14.25
<TABLE style="WIDTH: 419pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=558><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 40.5pt" height=54><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 55pt; HEIGHT: 40.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=54 width=73>Month</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; WIDTH: 364pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl83 width=485 colSpan=6>Feb-11</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #00ccff; HEIGHT: 16.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=22>Day</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>From</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>To</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Break</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Worked</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66>Comments</TD></TR><TR style="HEIGHT: 39.75pt" height=53><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 39.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl77 height=53>Mon </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>31-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>18:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>02:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>7.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl77 height=21>Tue</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>01-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>09:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>17:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>7.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 26.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=35>Wed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>02-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>10:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>19:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>8.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=22>Thu</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>03-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>10:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>18:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>7.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl77 height=21>Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>04-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=21>Sat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>05-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>08:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>18:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>14.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=21>Sun</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>06-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #969696; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 height=21>Total/week</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82>45.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78></TD></TR></TBODY></TABLE>


I use office 2007 on windows xp pro.

Many Thanks in advance.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
ndrepebx said:
Hi
I work on a 24hour Rota, and the shifts change and can be at different hours.
But
Hours between 08:00 and 20:00 are Standard ( 1hr = 1 hr )<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Hours between 20:00 and 08:00 are overtime and paid at time and half (1 hr = 1.5 hrs )<o:p></o:p>
Saturday and Sunday all hours are all overtime at 1.5<o:p></o:p>

I would like to know if there is a way of working out the total of hours per shift. but if there are overtime hours I would like to calculate them at 1.5.

Example below, Start work on Monday at 18:00 and finished at 02:00 on Tuesday morning so there are 6hrs x 1.5 and 2hrs x1.
6x1.5 = 9 + 2x1=11hrs.

My formula for Monday below shows the amount of hours worked =8 - 0.5 (wich is the break/lunch) =7.5

Monday formula =24*MOD(D4-C4,1)-E4
Saturday formula =(24*MOD(D9-C9,1)-E9)*1.5


A4-------------B4-----------C4---------D4------E4--------F4<o:p></o:p>
Day-----------Date--------- Start -----Finish --Break-----Hrs Worked
Monday------31/01/11------18:00-----02:00 ----0.5------ 7.5
Saturday-----05/02/11------08:00-----18:00-----0.5------14.25
<TABLE style="WIDTH: 419pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=558><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><TBODY><TR style="HEIGHT: 40.5pt" height=54><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 55pt; HEIGHT: 40.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=54 width=73>Month</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; WIDTH: 364pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl83 width=485 colSpan=6>Feb-11</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #00ccff; HEIGHT: 16.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=22>Day</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>From</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>To</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Break</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Worked</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00ccff; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66>Comments</TD></TR><TR style="HEIGHT: 39.75pt" height=53><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 39.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl77 height=53>Mon </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>31-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>18:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>02:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>7.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl77 height=21>Tue</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>01-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>09:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>17:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>7.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 26.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=35>Wed</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>02-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>10:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>19:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>8.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=22>Thu</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>03-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>10:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>18:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>7.50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl77 height=21>Fri</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>04-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=21>Sat</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>05-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>08:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>18:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>14.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=21>Sun</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>06-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #969696; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 height=21>Total/week</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82>45.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #969696; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78></TD></TR></TBODY></TABLE>


I use office 2007 on windows xp pro.

Many Thanks in advance.
Click to expand...

Or, is there a way of having the amount of hours worked from 08:00 to 20:00 in one cell and the amount of hours from 20:00 to 08:00 in another cell?
 
Upvote 0
did you ever get an answer? I have the same problem myself and just found your thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
829
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
593
epactheactor
E
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
349
esben
E
V
  • Question
Issue with scraping with VBA
Replies
1
Views
508
John_w
J

Forum statistics

Threads
1,214,957
Messages
6,122,466
Members
449,086
Latest member
kwindels

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top