How do I calculate the number of weekdays between two date fields - excluding weekends?

Hello Experts

I am trying to use the below code in a hidden field...

diffDays := (date_resolved - date_created) / 86400 + 1;
strtDay := @Modulo(@Weekday(date_created); 7);
endDay := @Modulo(@Weekday(date_resolved); 7);
result := (diffDays - endDay + strtDay - 8) * 5 / 7 - @Max(-2; -strtDay) - @Min(1; endDay) + 5 - strtDay + endDay;""

but I get nothing? Anything wrong I did?

Thanks
car701015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbonaciCommented:
Use this function:
@BusinessDays( startDates ; endDates ; daysToExclude ; datesToExclude )
0
car701015Author Commented:
Hi,

I get an error

FIELD: 'days': Incorrect data type for operator or @Function: Time/Date expected...

0
mbonaciCommented:
As an usage example (from Designer help), this function returns the number of business days between 01.01.2010. and 31.12.2010, excluding certain dates (holidays).
Numbers 1 and 7 represent weekdays you want to exclude (1 is Sunday and 7 is Saturday).

@BusinessDays( [01/01/2010]; [12/31/2010]; 1 : 7; [01/01/2010] : [01/15/2010] : [02/16/2010] : [05/28/2010] : [07/04/2010] : [09/03/2010] : [10/08/2010] : [11/22/2010] : [11/23/2010] : [12/25/2010] )
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

mbonaciCommented:
You can convert field values (if they are text) to date/time using @TextToTime.

0
car701015Author Commented:
man, I dont know what I am doing wrong here but geepers...

@BusinessDays(@ToTime(date_created) -@ToTime(resolved_Date) ; 1:7 )

still getting:

FIELD: 'days': Unable to interpret Time or Date
0
mbonaciCommented:
See the function signature, it's not '-' but ';' that separates the first two arguments:

@BusinessDays(@ToTime(date_created); @ToTime(resolved_Date); 1 : 7 )
0
car701015Author Commented:
Hi,

I got rid of the error now, yet it doesnt do the calculation

@If(resolved_date = "" | date_created = "" ; 0 ; @BusinessDays(@ToTime(date_created); @ToTime(resolved_date); 1 : 7 ))

If I leave this out

@If(resolved_date = "" | date_created = "" ; 0 ;

I get the error again...
0
mbonaciCommented:
Give me a few examples of field date_created i resolved_date contents.
Which type of fields they are?
0
mbonaciCommented:
To test this temporarily create "computed text" with formulas @ToTime(date_created) and then try to enter different date formats and click F9 between tries.
0
car701015Author Commented:
Hi,

I have added screen shots to a DOC file fyi...
Doc11.doc
0
mbonaciCommented:
I thought that the only problem here can be custom date format, but it creates no problems for me.
Clipboard01.jpg
Clipboard02.jpg
0
mbonaciCommented:
Try to extract only date using @Date, like this (cuz' you don't have to convert since the fields are already date/time):

@BusinessDays(@Date(date_created); @Date(resolved_date); 1 : 7 )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
car701015Author Commented:
That works, however I still had to show

@If(resolved_date = "" | date_created = "" ; 0; @BusinessDays(@Date(date_created); @Date(resolved_date); 1 : 7 ))

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.