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.

 

[barry houdini]

What result are you looking for exactly? If you have this as the start

2011/03/11 11(GMT)

and the end as

2011/03/16 8(GMT)

then what should the result be?

Do you have the times/dates in that actual format - in which case I assume they are text values not formatted date/times?

 

[deuce]

we would have a total of 57 hours or 2.3750 days

i.e. 104 hours total minus 47 holiday hours = 57/24(hours) or 2.3750 days.


Please correct me if I am wrong.

Hope this helps.

 

[deuce]

sorry barry, yes they are formatted as date/time values and not plain text values.

 

[barry houdini]

I'd expect that to be 68 hours, 11 on Friday, 1 on Sunday, 24 each on Monday and Tuesday and then 8 on Wednesday.

To get that result then with start time/date in A2 and end time and date in B2 try this formula in C2

=INT((B2-INT(A2+"1:00")+WEEKDAY(A2,3)+"1:00")/7)*"119:00"+WEEKDAY(B2+"1:00",3)+MOD(B2+"1:00",1)-WEEKDAY(A2+"1:00",3)-MOD(A2+"1:00",1)

format C2 as [h]:mm to show 68:00

 

[deuce]

Hi Barry, thanks for helping, the formula is correct with just a few final touches that need to be applied.

The formula is giving a -1 result where I think the starting hour is not being calculated in meaning it may be taking the bearing from the end of the starting hour to the end of the ending hour, rather it is supposed to calculate from the beginning of the starting hour to the end of the ending hour. As an example from 2011/03/11 (11:00 Server time) to 2011/03/14 (06:00 Server time) should give 19 hours but the result I get is 18 hours.

Additionally I am trying to display this as a result of days so 19 hours would be .7917 days. i.e. 19/24

Also how do we account for daylight savings time? The server details are GMT + 2 when DST is being observed GMT+1 when it is not being observed.

 

[barry houdini]

The working hours are in the range from Sunday 23:00 London Time to Friday 22:00 London time.

Given the above if the start time is 11 on Friday and end time is 6 on Monday then isn't that 11 hours on Friday and 7 on Sunday/Monday, giving a total of 18?

You can simply change the format of the result cell to number to get the result in decimal days

 

[deuce]

Hi Barry, no remember we are subtracting from the start date/time going forward until the end of the date/time, what you may be doing is subtracting the start of Friday until 11 which would give 11 hours indeed

However subtracting 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 00, 01, 02, 03, 04, 05, 06 would all give 20 hours not 19 (my mistake) as I stated.

 

[jasonb75]

I would agree with barry, 18 hours based on the information provided so far.

The working hours are in the range from Sunday 23:00 London Time to Friday 22:00 London time.

2011/03/11 (11:00 Server time)

Makes start time 10:00 GMT, so 12 hours to end of working hours at 22:00

2011/03/14 (06:00 Server time)

Makes finish time 05:00 GMT, 6 hours after working time start at 23:00

Added together, gives a total of 18 hours.

 

[deuce]

Hi Jasonb75, remember. I just provided a GMT reference thinking it would help in determining the final result.

I counted a total of 20 working hours from the dates mentioned basically the server time is not randomly set, it is based on central European time which is +1 GMT and +2 GMT when DST is being observed.

Lay out the hours as I did in my last example and count them going from March 11 to March 14 and you'd understand what I am talking about.

I find it hard to count the hours manually until after they have occurred too.

 

[deuce]

Okay some more information, the last working minute was 22:59 ST on the march 11th and start working time was 00:00 ST march 14th.

hope this clarifies on the matter.