Automatic finish date & time with lunch break and bank holidays

D

deckerp

Active Member
Joined
Feb 12, 2010
Messages
319
Office Version
  1. 365
Hi,
I would like a solution for the automatic calculation of the end date and end time for project tasks. I have already spent hours on the issue, thanks for any help on this.


The parameters are:
A1 = Start time 08:00
B1 = End Time 17:00
A2 = break lunch 12:00
B2 = back from lunch 13:00

Task parameters

A5 = start date 01/03/10 (entered manually)
B5 = start time 10:00 (entered manually)
C5 = duration 02:00 (hrs entered manually)
D5 = "end date" >>> (to be calculated exluding breaks and holidays)
E5 = "end time" >>> (to be calculated exluding breaks and holidays)


the next line should be filled in automaitically according to the hours needed and the previous end date & time

A6 = "start date" >>> (after line 5: to be calculated exluding breaks and holidays)
B6 = "start time" (after line 5: to be calculated exluding breaks and holidays)
C6 = duration 14:00 (entered manually)
D6 = "end date" >>> (to be calculated exluding breaks and holidays)
E6 = "end time" >>> (to be calculated exluding breaks and holidays)

and so on for every new line down.
 
Last edited:
Sort by date Sort by votes
Hi,

I'm not an Expert, but seeing as you haven't had any help so far I'll try my best to help out.

What does your table look like?
This is what I have...

<TABLE style="WIDTH: 305pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=406><COLGROUP><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 24pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl148 height=21 width=32> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=70>A</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=75>B</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=69>C</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=64>D</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=68>E</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl143 width=28> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl161>Start Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl163>End Time</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #c0c0c0" class=xl162 align=right>08:00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" id=td_post_2213573 class=xl164 align=right>17:00</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl159></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl159></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl159></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl163>Start Lunch</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl152>End Lunch</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl164 align=right>12:00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl160 align=right>13:00</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>5</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl151>Start Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl158>Start Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl158>Duration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl158>End Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl152>End Time</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl153> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl155> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl156> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl156> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl156> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl157> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl150 height=21> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl147> </TD></TR></TBODY></TABLE>

What do you expect the results to look like?

Ak
 
Upvote 0
the table looks similar to yours, but added bank holidays-
I have made some comments into your table.




Akashwani said:
Hi,

I'm not an Expert, but seeing as you haven't had any help so far I'll try my best to help out.

What does your table look like?
This is what I have...



<table style="width: 305pt; border-collapse: collapse;" width="406" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 24pt;" width="32"><col style="width: 53pt;" width="70"><col style="width: 56pt;" width="75"><col style="width: 52pt;" width="69"><col style="width: 48pt;" width="64"><col style="width: 51pt;" width="68"><col style="width: 21pt;" width="28"></colgroup><tbody><tr style="height: 15.75pt;" height="21"><td style="border-style: solid none none solid; border-color: windowtext rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: 1pt medium medium 1pt; background-color: transparent; width: 24pt; height: 15.75pt;" class="xl148" width="32" height="21">
</td><td style="border-style: solid none none; border-color: windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: 1pt medium medium; background-color: transparent; width: 53pt;" class="xl142" width="70">A</td><td style="border-style: solid none none; border-color: windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: 1pt medium medium; background-color: transparent; width: 56pt;" class="xl142" width="75">B</td><td style="border-style: solid none none; border-color: windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: 1pt medium medium; background-color: transparent; width: 52pt;" class="xl142" width="69">C</td><td style="border-style: solid none none; border-color: windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: 1pt medium medium; background-color: transparent; width: 48pt;" class="xl142" width="64">D</td><td style="border-style: solid none none; border-color: windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: 1pt medium medium; background-color: transparent; width: 51pt;" class="xl142" width="68">E</td><td style="border-style: solid solid none none; border-color: windowtext windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: 1pt 1pt medium medium; background-color: transparent; width: 21pt;" class="xl143" width="28">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">1</td><td style="border-style: solid none solid solid; border-color: windowtext rgb(192, 192, 192) windowtext windowtext; border-width: 1pt medium 0.5pt 1pt; background-color: transparent;" class="xl161">Start Time</td><td style="border-style: solid; border-color: windowtext; border-width: 1pt 1pt 0.5pt; background-color: transparent;" class="xl163">End Time</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15.75pt;" height="21"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15.75pt;" class="xl149" align="right" height="21">2</td><td style="border-style: none none solid solid; border-color: windowtext rgb(192, 192, 192) windowtext windowtext; border-width: medium medium 1pt 1pt; background-color: transparent;" class="xl162" align="right">08:00</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 1pt 1pt; background-color: transparent;" id="td_post_2213573" class="xl164" align="right">17:00</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;" class="xl159">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;" class="xl159">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;" class="xl159">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">3</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 1pt 0.5pt; background-color: transparent;" class="xl163">Start Lunch</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 1pt 0.5pt 0.5pt; background-color: transparent;" class="xl152">End Lunch</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15.75pt;" height="21"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15.75pt;" class="xl149" align="right" height="21">4</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 1pt 1pt; background-color: transparent;" class="xl164" align="right">12:00</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 1pt 1pt 0.5pt; background-color: transparent;" class="xl160" align="right">13:00</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15.75pt;" height="21"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15.75pt;" class="xl149" align="right" height="21">5</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border: medium none rgb(192, 192, 192); background-color: transparent;">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">6</td><td style="border-style: solid; border-color: windowtext; border-width: 1pt 0.5pt 0.5pt 1pt; background-color: transparent;" class="xl151">Start Date</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 1pt 0.5pt 0.5pt medium; background-color: transparent;" class="xl158">Start Time</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 1pt 0.5pt 0.5pt medium; background-color: transparent;" class="xl158">Duration</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 1pt 0.5pt 0.5pt medium; background-color: transparent;" class="xl158">End Date</td><td style="border-style: solid solid solid none; border-color: windowtext; border-width: 1pt 1pt 0.5pt medium; background-color: transparent;" class="xl152">End Time</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">7</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt 1pt; background-color: transparent;" class="xl153">01/03/10
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">08:00
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">02:00
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">autom.
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 1pt 0.5pt medium; background-color: transparent;" class="xl154">autom.
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">8</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt 1pt; background-color: transparent;" class="xl153">autom.</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">autom.</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">16:00
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">autom.</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 1pt 0.5pt medium; background-color: transparent;" class="xl154">autom.</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">9</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt 1pt; background-color: transparent;" class="xl153">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">15:00
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 1pt 0.5pt medium; background-color: transparent;" class="xl154">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">10</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt 1pt; background-color: transparent;" class="xl153">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">01:30
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 1pt 0.5pt medium; background-color: transparent;" class="xl154">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">11</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt 1pt; background-color: transparent;" class="xl153">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">04:15
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 1pt 0.5pt medium; background-color: transparent;" class="xl154">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15pt;" height="20"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15pt;" class="xl149" align="right" height="20">12</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 0.5pt 1pt; background-color: transparent;" class="xl153">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">06:00
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 0.5pt medium; background-color: transparent;" class="xl145">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 1pt 0.5pt medium; background-color: transparent;" class="xl154">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15.75pt;" height="21"><td style="border-style: none none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium medium 1pt; background-color: transparent; height: 15.75pt;" class="xl149" align="right" height="21">13</td><td style="border-style: none solid solid; border-color: windowtext; border-width: medium 0.5pt 1pt 1pt; background-color: transparent;" class="xl155">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 1pt medium; background-color: transparent;" class="xl156">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 1pt medium; background-color: transparent;" class="xl156">35:00
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 0.5pt 1pt medium; background-color: transparent;" class="xl156">
</td><td style="border-style: none solid solid none; border-color: windowtext; border-width: medium 1pt 1pt medium; background-color: transparent;" class="xl157">
</td><td style="border-style: none solid none none; border-color: rgb(192, 192, 192) windowtext rgb(192, 192, 192) rgb(192, 192, 192); border-width: medium 1pt medium medium; background-color: transparent;" class="xl144">
</td></tr><tr style="height: 15.75pt;" height="21"><td style="border-style: none none solid solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) windowtext windowtext; border-width: medium medium 1pt 1pt; background-color: transparent; height: 15.75pt;" class="xl150" height="21">
</td><td style="border-style: none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium 1pt; background-color: transparent;" class="xl146">
</td><td style="border-style: none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium 1pt; background-color: transparent;" class="xl146">
</td><td style="border-style: none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium 1pt; background-color: transparent;" class="xl146">
</td><td style="border-style: none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium 1pt; background-color: transparent;" class="xl146">
</td><td style="border-style: none none solid; border-color: rgb(192, 192, 192) rgb(192, 192, 192) windowtext; border-width: medium medium 1pt; background-color: transparent;" class="xl146">
</td><td style="border-style: none solid solid none; border-color: rgb(192, 192, 192) windowtext windowtext rgb(192, 192, 192); border-width: medium 1pt 1pt medium; background-color: transparent;" class="xl147">
</td></tr></tbody></table>

What do you expect the results to look like?

Ak
Click to expand...
 
Upvote 0
another problem is that if a task finishs at the last working hour of a day (17:00) then the next activity in the next row should start the next working day; meaning I cannot say A8=D7 and B8=E7. instead there must be a formula with workdays
 
Upvote 0
What are the dates of your Bank Holidays?
Are you working weekends?

In this example,
What would define the next Start Date?
What would define the Start Time?
What would define the Duration?

<TABLE style="WIDTH: 343pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=458><COLGROUP><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 24pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl148 height=21 width=32> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=75>A</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=75>B</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 52pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=69>C</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=111>D</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=68>E</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl143 width=28> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl163>Start Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl163>End Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl172>Total</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #c0c0c0" class=xl164 align=right>08:00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #c0c0c0" id=td_post_2213573 class=xl164 align=right>17:00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl166 align=right>09:00</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl162></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl162></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl165>Start Lunch</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #c0c0c0" class=xl170>End Lunch</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl153> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl166 align=right>12:00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #c0c0c0" class=xl171 align=right>13:00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl166 align=right>01:00</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>5</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl151>Start Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl159>Start Time</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl159>Duration</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl159>End Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl152>End Time</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl167 align=right>01/03/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl161 align=right>10:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl169 align=right>2:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl160 align=right>01/03/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl168 align=right>12:00</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl155> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl155> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl155> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl155> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl154> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl155> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=21 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl156> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl157> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl157> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl157> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl158> </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl150 height=21> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl147> </TD></TR></TBODY></TABLE>


Ak
 
Upvote 0
the duration is defined in hours (eg: 02:00 or 04:30)

working hours:

<table x:str="" style="border-collapse: collapse; width: 154pt;" width="205" border="0" cellpadding="0" cellspacing="0"><col style="width: 95pt;" width="126"> <col style="width: 59pt;" width="79"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl24" style="height: 12pt; width: 95pt;" width="126" height="16">Start of day</td> <td class="xl26" style="border-left: medium none; width: 59pt;" x:num="0.33333333333333331" width="79">8:00</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl25" style="border-top: medium none; height: 12pt;" height="16">End of day</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" x:num="0.70833333333333337">17:00</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl27" style="height: 12pt;" height="16">Start break</td> <td class="xl28" style="border-left: medium none;" x:num="0.5">12:00</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl24" style="border-top: medium none; height: 12pt;" height="16">End break</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:num="0.54166666666666663">13:00</td> </tr> </tbody></table>

bank holidays are dates in a separate table (below copy paste)

<table x:str="" style="border-collapse: collapse; width: 130px; height: 899px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 59pt;" width="79"> <tbody><tr style="height: 12pt;" height="16"> <td class="xl22" style="height: 12pt; width: 59pt;" x:num="40179" width="79" height="16">Fr, 01.01.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40184" height="16">Mi, 06.01.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40270" height="16">Fr, 02.04.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40272" height="16">So, 04.04.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40273" height="16">Mo, 05.04.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40299" height="16">Sa, 01.05.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40311" height="16">Do, 13.05.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40321" height="16">So, 23.05.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40322" height="16">Mo, 24.05.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40332" height="16">Do, 03.06.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40454" height="16">So, 03.10.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40483" height="16">Mo, 01.11.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40537" height="16">Sa, 25.12.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl23" style="border-top: medium none; height: 12pt;" x:num="40538" height="16">So, 26.12.10</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl24" style="height: 12pt;" x:num="40544" height="16">Sa, 01.01.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40655" height="16">Fr, 22.04.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40658" height="16">Mo, 25.04.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40664" height="16">So, 01.05.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40696" height="16">Do, 02.06.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40707" height="16">Mo, 13.06.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40717" height="16">Do, 23.06.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40819" height="16">Mo, 03.10.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40848" height="16">Di, 01.11.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl22" style="border-top: medium none; height: 12pt;" x:num="40902" height="16">So, 25.12.11</td> </tr> <tr style="height: 12pt;" height="16"> <td class="xl23" style="border-top: medium none; height: 12pt;" x:num="40903" height="16">Mo, 26.12.11</td> </tr> </tbody></table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Total Break in minutes between Employees Break time and Break Start Time
2
Replies
17
Views
527
alansidman
alansidman
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
502
myall_blues
myall_blues
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
239
cooleomter
C
R
  • Solved
Adding Hours & Minutes to Dates & Times w/in Working Hours
2
Replies
11
Views
810
Asbestos_Jen
Asbestos_Jen
H
  • Solved
Scatter Chart to Show Durations
Replies
2
Views
290
HJay
H

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