6 Replies Latest reply on Jan 7, 2014 2:11 PM by genemapper

# Weekdays between two dates

Hi everyone

Am new to forum but not exactly new to FM

I would like to find the number of weekdays (monday to friday) between two dates. my attempts so far have been complicated and have all failed.

Any ideas for a simple solution?? thanks

• ###### 1. Re: Weekdays between two dates

/*==============================================================
CustomFunction    BusinessDays ( startDate ; endDate )
=============================================================*/
Let (
[
THEDATE = startDate ;
BD =
not (
DayOfWeek ( THEDATE ) = 1 or     // Sunday
DayOfWeek ( THEDATE ) = 7     // Saturday
)
] ;
Case (
not ( IsEmpty ( startDate ) or IsEmpty ( endDate ) ) and
0 ≤ endDate - startDate ;

BD
+ BusinessDays ( startDate + 1 ; endDate )
)
)

• ###### 2. Re: Weekdays between two dates

/* ==============================================================

CustomFunction   WeekDays ( sD ; eD )

============================================================= */

Let ( [

dw = DayOfWeek ( sD ) ;

wd = not ( dw = 1 or dw = 7 )

] ;

Case (

eD < sD ; "Not valid" ;

IsEmpty ( sD ) or not Count ( sD ; eD ) ; "" ;

IsEmpty ( eD ) or sD = eD ; wd ;

wd + WeekDays ( sD + 1 ; eD )

)

)

If you want the actual business days, you need another CF and/or a table with your applicable bank holiday dates, and add a check to the wd variable calculation (like “… or dw = 7 or isHoliday ( sD ) )” ).

• ###### 3. Re: Weekdays between two dates

pixi wrote:

This CF seems to sport everything but the kitchen sink. What it doesn't do is answer the OP's original question …

• ###### 4. Re: Weekdays between two dates

Let( [

total.days = end.date - start.date + 1 ;

start.day = DayOfWeek ( start.date ) ;

end.day = DayOfWeek ( end.date ) ;

start.balance = Case ( start.day = 7 ; -2 ; start.day = 1 ; -1 ; 0 ) ;

end.balance = Case ( end.day = 7 ; -1 ; end.day = 1 ; -2 ; 0 ) ;

total.days.adjusted = total.days + start.balance + end.balance ;

total.weeks =  Div ( total.days.adjusted ; 7  ) ;

total.week.ends = total.weeks  * 2 ;

];

result

)

• ###### 5. Re: Weekdays between two dates

Dear David

thanks but this didn't work. I tried a similar approach. See Post from user 14047

• ###### 6. Re: Weekdays between two dates

Hi User14047

Many thanks. Super CF. Took 2 mins to set up and runs perfectly....