Network days Function.

[deuce]

Hi, I am using the network days function to define business days but I am not clear on how to set start and end limits for the hours that would be the first starting hour for the first business day and last ending hour for the last business day.

Is there some way to be any more grainy with this function?

thanks in advance.

 

[jasonb75]

11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 00, 01, 02, 03, 04, 05, 06

Using pre-school counting methods, like your one above, I still only make it 18 hours, not 19 or even 20 as you seem to manage.

In the table below, the red times show working hours for March 11, green show march 14, with each individual hour listed as a starting and ending minute, to represent a 1 hour period, resulting in a simple 1+1+1+... in the third column to give a total number of hours.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 74px"><COL style="WIDTH: 67px"><COL style="WIDTH: 43px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Hours start</TD><TD>Hour end </TD><TD>Hours</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">11:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">11:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">12:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">12:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">13:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">13:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">14:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">14:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">15:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">15:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">16:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">16:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">17:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">17:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">18:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">18:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">19:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">19:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">20:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">20:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">21:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">21:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">22:00</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">22:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">23:00</TD><TD style="TEXT-ALIGN: right">23:59</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">00:00</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">00:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">01:00</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">01:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">02:00</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">02:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">03:00</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">03:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">04:00</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">04:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">05:00</TD><TD style="TEXT-ALIGN: right; COLOR: #008080">05:59</TD><TD style="TEXT-ALIGN: right">1</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[deuce]

hi jason b, I see what your saying and also agree with it but will try it and update if i come across any problems.

thanks )

 

[jasonb75]

Just to throw another spanner in the works.

2011/03/14 (00:00:00 server time) to 2011/03/18 (22:59:59 server time)

Is that 5 days or 4.958?

How about

2011/03/18 (00:00:00 server time) to 2011/03/18 (22:59:59 server time)

Or

2011/03/17 (23:00:00 server time) to 2011/03/18 (22:59:59 server time)

On technicality, the first should be 1.00 as it spans the whole of friday, the second should be 1.00 as it covers 24 hours, which is 1 day but should also be 1.0417 which is the whole of friday plus 1 hour.

Now consider

2011/03/17 (12:00:00 server time) to 2011/03/18 (11:59:59 server time)

Again, technically 1 day (24 hours), but technicaly not as you have 0.5 from 03/17 plus 0.5217 from 03/18 which doesn't add up to 1

Back to your original example,

2011/03/11 (11:00:00 server time) to 2011/03/14 (06:00:00 server time)

(12 hours from a 23 hour day = 0.5217) + (6 hours from 24 hour day =0.25) = 0.7717 days or 0.75 days?

Seems to be a very bad idea to work with decimal time when your days are inconsisant in length, whichever way you go with this you will inevitably end up with "incorrect" results somewhere.

 

[deuce]

hi jason b75, so what can I do about fixing this? the days are not inconsistent as the days are 24 hours in length giving 168 hours in the complete 7 days of the week i.e. 24*7 = 168 but we only need to subtract the holiday hours from the 168 to get the net total hours that can be used in 1 week.

If time measurement from point A to B is spanning within the net total the count will be true but if it is spanning past the net total then the balance of hours will need to be added to the net total minus any second instances of holiday hours, again we end up with the net total and if we have to add any more hours if they span within the net total in the 3rd instance the count will be true however if the 3rd holiday period is factored in we have to subtract that time bracket from it also then add any excess...

Would you like to try a calculation from Feb 14, 2011 14:00:00 ST to March 18, 2011 22:59 ST?

 

[deuce]

Hi, there has been a change on the server times to make it more compatible with the calculations, they have changed the server times and server time closing hours from 5 PM EST every Friday to 1 PM every Sunday so between these 2 times will be a holiday period.

Can the formula please be changed to reflect this update?

 

[jasonb75]

hi jason b75, so what can I do about fixing this? the days are not inconsistent as the days are 24 hours in length giving 168 hours in the complete 7 days of the week i.e. 24*7 = 168 but we only need to subtract the holiday hours from the 168 to get the net total hours that can be used in 1 week.

If time measurement from point A to B is spanning within the net total the count will be true but if it is spanning past the net total then the balance of hours will need to be added to the net total minus any second instances of holiday hours, again we end up with the net total and if we have to add any more hours if they span within the net total in the 3rd instance the count will be true however if the 3rd holiday period is factored in we have to subtract that time bracket from it also then add any excess...

Would you like to try a calculation from Feb 14, 2011 14:00:00 ST to March 18, 2011 22:59 ST?

What result would you expect for that period, and for each of the periods in my previous post?

Hi, there has been a change on the server times to make it more compatible with the calculations, they have changed the server times and server time closing hours from 5 PM EST every Friday to 1 PM every Sunday so between these 2 times will be a holiday period.

Can the formula please be changed to reflect this update?

At first glance that looks extremely incompatible.

 

[deuce]

hi, forget all the holiday periods from the past the CET time and +1 and +2GMT, and look at those new holiday hours from my last post i.e. from 5PM EST friday to 1pm EST sunday.

So we now have about about 31 holiday hours if my rough calculations are correct.

can the formula from post 14 be modified to reflect this?

 

[jasonb75]

hi, forget all the holiday periods from the past the CET time and +1 and +2GMT, and look at those new holiday hours from my last post i.e. from 5PM EST friday to 1pm EST sunday.

So we now have about about 31 holiday hours if my rough calculations are correct.

can the formula from post 14 be modified to reflect this?

1PM or 1AM?

The existing 1PM would give 44 holiday hours, 1AM would give 32 which is much closer to your rough calculation.

 

[deuce]

sorry, it's 1 pm EST sunday or 1pm sunday afternoon.