CODEWITHSUNDEEP
reporting-services
Quazi200
Quazi200

Reputation: 13

first business day of next month

I'm trying to set the default value for a date parameter to the first business day of next month, that is if the 1st of the month is a Saturday or a Sunday, it selects the following Monday. I've got this but for some reason when I try to run the report it says an error occurred during local report processing and I can't figure out what's going wrong. Can anyone help?

= iif (datepart("dw", dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))) = 7,
dateadd("m",1,DateAdd("d",3-DatePart("d",Today()),Today())),
iif (datepart("dw", dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))) = 1,
dateadd("m",1,DateAdd("d",2-DatePart("d",Today()),Today())),
dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))))

Upvotes: 1

Views: 218

Answers (2)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try:

=Switch(
WeekDay(DateSerial(Today.Year,Today.Month,1).AddMonths(1),FirstDayOfWeek.Monday)=6,
  DateSerial(Today.Year,Today.Month,1).AddMonths(1).AddDays(2),
WeekDay(DateSerial(Today.Year,Today.Month,1).AddMonths(1),FirstDayOfWeek.Monday)=7,
  DateSerial(Today.Year,Today.Month,1).AddMonths(1).AddDays(1),
true, DateSerial(Today.Year,Today.Month,1).AddMonths(1)
)

Assuming your first date of week monday.

Hope it helps.

Upvotes: 0

mhep
mhep

Reputation: 2139

=Today.AddMonths(1).AddDays(-Today.Day + 1).AddDays(
    SWITCH(
    Today.AddMonths(1).AddDays(-Today.Day + 1).DayOfWeek = DayOfWeek.Sunday, 1,
    Today.AddMonths(1).AddDays(-Today.Day + 1).DayOfWeek = DayOfWeek.Saturday, 2,
    True, 0
))

This returns the 1st of the following month:

Today.AddMonths(1).AddDays(-Today.Day + 1)

The switch statement then determines how many days to add based on the day of the 1st of the following month:

.AddDays(SWITCH(
        Today.AddMonths(1).AddDays(-Today.Day + 1).DayOfWeek = DayOfWeek.Sunday, 1,
        Today.AddMonths(1).AddDays(-Today.Day + 1).DayOfWeek = DayOfWeek.Saturday, 2,
        True, 0
    ))

Upvotes: 0

Related Questions