Hi @kanupindi ,

Try this formula:

=DateAdd('1/1/1900', 'w', DateDiff('w', '1/1/1900', Today())-1)

Laura

Hi @lauraj46, thank you for your swift response as always! I tried this on the GI and seemed to work. I still need to check the report designer as well. With this formula, would the dates change dynamically?

Hi @kanupindi ,

Glad that worked!  It will change dynamically based on the current date (Today() ).

Laura

Hey @lauraj46 , it worked on the GI but not on the report writer/ report designer. We get the below error, is there a different formula that we can use on the report writer? thank you!

Hi @kanupindi ,

There seem to be some idiosyncracies with the date function in report designer.  You can use CDate to cast 1/1/1900 as a date, but I had to adjust the first date forward to 5/7/1900 to get the correct date as an end result.  Not sure why it should be 5/7/1900, but this formula does seem to work:

=DateAdd(CDate('5/7/1900'), 'w', DateDiff('w', CDate('1/1/1900'), CDate(Today()))-1)

A different approach is to build the formula based on the day of the week.  The idea is to subtract a certain number of days based on the day of the week.

Beware though.  In my experiments the DayOfWeek function operates differently in a GI vs the Report Designer.  In a GI the DayOfWeek function returns 1-7 beginning with Sunday.  In the Report Designer, the DayOfWeek function appears to return 0-6 beginning with Monday.  

In the Report Designer, I was able to get the correct behavior using this formula:

=IIf(DayOfWeek(Today())=0, DateAdd(Today(), 'd', -13), DateAdd(Today(), 'd', -1*DayOfWeek(Today())-6))

Hope that helps!

Laura

Hi @lauraj46, thank you very much! That worked!! Have a great day!