Home |
Search |
Today's Posts |
#1
|
|||
|
|||
NETWORKDAY function problem - starting on weekends
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 |
#2
|
|||
|
|||
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 wrote in message ups.com... 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 |
#3
|
|||
|
|||
Biff,
I want the elapsed time based on network days and business hours. Lourens |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 wrote in message ups.com... 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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spellnumber | Excel Worksheet Functions | |||
Excel Send To function problem | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions |