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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
644
felixstraube
F
P
  • Question
Calculate time between start and finish minus the lunch break
2
Replies
13
Views
713
paulalex93
P
B
  • Solved
Projecting End date by accounting for holidays
Replies
4
Views
262
BB77
B
S
  • Solved
Calculate Revert Time
Replies
6
Views
405
felixstraube
F
A
  • Question
Add a specified number of working hours to a date & time, given working day start, end and break times and holidays
Replies
2
Views
226
AquaBuoy
A

Forum statistics

Threads
1,214,651
Messages
6,120,742
Members
448,989
Latest member
mariah3

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