[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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
0
car701015
Asked:
car701015
  • 8
  • 5
1 Solution
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now