working days in date range calc

[Robert Collins]

After a bit of searching around I have found this calc to work out the number of working days in a a given date range (based on not working Saturday and Sunday)

it relies on a field called StartDate and one called EndDate

Int((EndDate - StartDate)/7) * 5 + If(DayofWeek(EndDate) < DayofWeek(StartDate),

Min(5, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)),

If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) -

DayofWeek(StartDate), 0)) + 1

I would like to modify it so it it only counts Sundays as non working days. so I thought this would suffice. but some months are not calculated right. This is what I have so far.

Int((EndDate - StartDate)/7) * 6 + If(DayofWeek(EndDate) < DayofWeek(StartDate),

Min(6, DayofWeek(EndDate) - 1) + Max(0, 6-DayofWeek(StartDate)),

If(DayofWeek(StartDate) < 7, Min(6,DayofWeek(EndDate)) -

DayofWeek(StartDate), 0)) + 1

can anybody see where I am going wrong.

Thanks

Edited November 25, 2008 by Guest

[comment]

Try:

Let ( [

n = EndDate - StartDate + 1 ;

w = Div ( n ; 7 ) ;

r = Mod ( n ; 7 ) ;

d = Mod ( StartDate - 1 ; 7 )

] ;

6 * w + r - ( d + r > 6 )

)

Edited November 25, 2008 by Guest
Fixed a typo that caused the calc to count Saturdays instead of Sundays.

[Robert Collins]

thank you very much, that works great.

I only use this information in a preview layout.

Should I get my report script to set a calculated result into a global field or is it best to allocate a calc field to hold this (and if so, should this be a global field to save duplicate info across all records?)

thanks

[comment]

Assuming your StartDate and EndDate are global fields, you can make this a calculation field - it will be forced to unstored and therefore only calculated when and where needed.

Or you could have your report script set a global to the result - can't see much difference between the two, except perhaps that the global could be reused for other purposes?

[Robert Collins]

actually , I want my script to be a report for the current month

and I wanted the following info.

Rather than having two fields hold the start and end date , I want to hardcode the current month start and end date into your calc.

Date(Month(Get(CurrentDate)) ; 1; Year(Get(CurrentDate))) should get me the first of the current month and :

Date(Month(Get(CurrentDate)) + 1 ;0 ; Year(Get(CurrentDate)))

should get me the the last day of the month.

But what to I replace in your calc to get this to happen.

Also now need another calc that shows the remaining working days for the current month.

sorry if this is a bit muddled!

thanks

Edited November 25, 2008 by Guest
I have got a bit muddled up here!

[comment]

I'm afraid you have lost me somewhere. The formula calculates the number of work days in the range between StartDate and EndDate (inclusive). Changing d to refer to a date other than starting date of the range will break it.

[Robert Collins]

Sorry about that I shall start again (change of plan!)

I need to modify your calc so rather than refer to fields StartDate and EndDate, I need to hardcode the startdate as the 1st of the current month and the enddate will now be the last day of the current month.

to complete this, I finally need a calc to show the remaining working days for the current month.

many thanks

[comment]

Perhaps it would be convenient to turn the formula into a custom function of WorkDays ( startDate ; endDate ) and call it each time with the required parameters:

For the entire current month:

Let ( [

today = Get ( CurrentDate ) ;

m = Month ( today ) ;

y = Year ( today )

] ;

WorkDays ( Date ( m ; 1 ; y ) ; Date ( m + 1 ; 0 ; y ) )

)

For the remainder of current month:

Let ( [

today = Get ( CurrentDate )

] ;

WorkDays ( today ; Date ( Month ( today ) + 1 ; 0 ; Year ( today ) ) )

)

[Robert Collins]

thanks , that makes sense.