Search code examples
reporting-servicesexpressiondateadddateinterval

SSRS Expression for current monday and current friday

I have the following expression for a text box on a report in SSRS

="TR1's Sent Between "+Format(dateadd(DateInterval.Day,1-WeekDay(Today), Today), "dd/MM/yyyy")

this returns sundays date rather than mondays. does anyone know how to show the current monday's date and current fridays? if you could explain each section so i understand the format that would be great

Solution

  • Try using these two expression:

    For Mondays you can use something like this:

    =Format(Today.AddDays(1-WeekDay(Today,FirstDayOfWeek.Monday)),"dd/MM/yyyy")

    For Fridays you can use:

    =FORMAT(Today.AddDays(5-WeekDay(Today,FirstDayOfWeek.Monday)),"dd/MM/yyyy")

    The first problem with your expression is that you are not specifying the first day of week in the WeekDay function, if your expression is returning the sunday date your system default first day of week is Sunday.

    My expression takes today's date and add/substract the number of days required to get the first day of week: Monday. As I set the first day of week in my expression by using FirstDayOfWeek.Monday it calculates 1-1 = 0 it will add zero days to the today's date since today is monday (at least now in Colombia).

    For Friday is similar, Friday is the 5 day of the week so you have to add/substract the required number of days to the today date to get its date. 5-1 = 4 (5 - 1 Today's day of week, taking in account monday is the first day), now just add 4 days to the today date and it will return the friday date.

    Let me know if this helps.