NETWORKDAY function problem - starting on weekends

[pentzol]

Hi,

I have used the function that Chip Pearson has supplied on his website
which calculates the difference between 2 dates. My problem is just
when the first date I supply to the function is on the saturday or the
sunday then it adds another day to the result.
example : 28-MAY-05 08:30 till 30-MAY-05 11:15

The time I want is on the 30th (Monday) from 9:00 till 11:15 but I get
an extra day and 7 hours using the formula.

Workhours is from 08:00 till 17:00 on a week day.

If the time started is before 8:00 on a Saturday I can change it to the
friday 17:00 and then the formula works again, but after 08:00 on a
saturday I am back where I started.

The other options is to create 2 new start and end date columns with
only working dates, but I will need to evaluate each date first to see
if it is a weekend and then get the next wotking day at 8:00 .

Any ideas how I can get around that if the range starts or ends on a
weekend or how I can test for a working day on a date supplied ?

Regards
Lourens

 

[Biff]

Hi!

Not sure what you're looking for.

Do you want networkdays or do you want an elapsed time based on networkdays
and business hours?

Biff

 

[pentzol]

Biff,

I want the elapsed time based on network days and business hours.

Lourens

 

[pentzol]

or if possible - if i have a date to check if it is a workday or not
and if it is not, then I want the previous or next working day

 

[Biff]

example : 28-MAY-05 08:30 till 30-MAY-05 11:15

The time I want is on the 30th (Monday) from 9:00 till 11:15 but I get
an extra day and 7 hours using the formula.

Workhours is from 08:00 till 17:00 on a week day.

If the normal business hours are from 8:00 to 17:00 then why do you want:
"from 9:00 till 11:15 "

Let me ask you this, what result do you expect?

A1 = 28-MAY-05 08:30
B1 = 30-MAY-05 11:15

=IF(NETWORKDAYS(A1,A1)=1,17/24-MOD(A1,1),0)
+IF(NETWORKDAYS(B1,B1)=1,MOD(B1,1)-8/24,0)+NETWORKDAYS(A1+1,B1-1)*9/24

Returns (in h:mm format): 3:15

May 30 2005 was the Memorial Day holiday so I assume you do not want to use
the excluded holidays argument for the Networkdays function. If you did
include that argument and had May 30 2005 listed then the result would have
been 0.

Biff

 

[pentzol]

Biff, thanks for the reply.
Sorry, typo on my part - I meant from "08:00 tiill 11:15"

Great that is what I am looking for , I will have a go at it.

Regards
Lourens