Formula to Convert Week # (i.e. W24) to actual date for all reports

Hello Jennifer,

My suggestion would be to create a Custom Field of type text (I called mine "Week") on the Work Item entity and set its default value to:

Left(ToString($StartDate - Days(Case(ToString($StartDate, "ddd"), "Mon", 0, "Tue", 1, "Wed", 2, "Thu", 3, "Fri", 4, "Sat", 5, "Sun", 6, 999999))), 10)

If it does not calculate, then update the field using a Scheduled Workflow Rule (run it only once) and the same formula. Also, create a Workflow Rule to update the value of "Week" field whenever Start Date is changed using the same formula. Make sure the Workflow Rules run only on Work Items with a non-blank Start Date field. Start Date can be changed to be any other date field.

What does the formula do? It converts a date into a date that is a Monday that week and essentially eliminates the need to group by week in the Reports module. E. g. 22/05/17 -> 22/05/17; 23/05/17 -> 22/05/17; 24/05/17 -> 22/05/17; etc.

Result when selecting "Week" as the X-axis:

Please let me know if this is helpful.

Thank you so much for offering assistance, as I was able to get this to work. I adjusted mine to Due Date but will also take advantage of the start date option as well. The only thing I had to do before the new Custom Field was visible for reporting is check the check box in the 'Edit Field' location that makes it searchable. Once I did that, everything displayed wonderfully. Thanks again!!

Jennifer

I would like to know how to implement this same idea but to display the MONTH number as the actual name ("2017 - 5" displayed as "2017 - May").

Hello Sarah,

The following formula will convert a date into text (e. g. 25/07/2017 -> 2017 - Jul):

ToString(Year($StartDate)) + " - " + ToString($StartDate, 'MMM')

You can replace the $StartDate with any other date.

Please let me know if this helps.