Maximum working day in currentmonth

[Bunting]

Good morning,

I am trying to establish the dates (dd/mm/yyyy) of the first and last workings days of each month and would like to return the results in two separate formulas;
- (@First working date in currentmonth)
- (@Last working date in currentmonth)

I define a working day as day in [1-5]

Any ideas?

 

[dgillz]

Write a formula to determine how many days to add the the first of the month to get to a working day:

@Begnum
If dayofweek(minimum(monthtodate)) in 1 to 5 then 0
else if dayofweek(minimum(monthtodate)) = 6 then 2 else 1

Write a similar formula for the end of the month:

@endnumb
if dayofweek(dateadd("m",1,minimum(monthtodate))-1) in 1 to 5 then 0
if dayofweek(dateadd("m",1,minimum(monthtodate))-1) =6 then -1 else -2

Finally write 2 formulas to get your desired start and end dates:

@startdate
if {@begnumb} = 0 the minimum(monthtodate) else
dateadd("d",{@begnumb},minimum(monthtodate))

@enddate
if {@endnumb}= 0 then dateadd("m",1, minimum(monthtodate))-1 else
dateadd("d",{@endnumb},dateadd("m",1, minimum(monthtodate))-1)


Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[IanWaterman]

Crystal designates Monday as 2, thus working days will be 2-6.

@firstwrkday

If dayofweek(maximum(lastfullmonth)+1) = 7 then
maximum(lastfullmonth)+3) else
If dayofweek(maximum(lastfullmonth)+1) = 1 then
maximum(lastfullmonth)+2) else
maximum(lastfullmonth)+1)

@lastwrkday

If dayofweek(Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-1) = 1 then
Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-3 else
If dayofweek(Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-1) = 7 then
Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-2

Hope fully my parentheses are correct.

Ian

 

[Bunting]

Many thanks for the prompt respones:
Ian, the Lastwrokingday in month formula that you kindly posted does not appear to work for dates after February, I can't work out why, any thoughts?

If dayofweek(Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-1) = 1 then
Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-3 else
If dayofweek(Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-1) = 7 then
Date(year(dateadd("m", 1,Currentdate)),
month(dateadd("m", 1,Currentdate)), 01)-2

 

[IanWaterman]

Sorry forgot the last default condition. Doh!

add this

else
Date(year(dateadd("m", 1,Currentdate)),month(dateadd("m", 1,Currentdate)), 01)-1

Ian