Post

4 followers Follow
1
Avatar

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

Working on executive reports and where we always struggle is with the way you all display weeks or months. Months are easier to understand because there are 12, but when you want to look at projected go lives for 100+ customers over the next year by week, it is very difficult to translate W18 or W27.

Has anyone been able to resolve this hurdle with a formula? I see that someone else posted the same concern in 2015, so it's been an enhancement for quite sometime and looks to be that way for awhile. 

https://success.clarizen.com/hc/en-us/community/posts/206713927-Week-commending-date-rather-than-W27-on-reports-?input_string=Formula%20to%20Convert%20Week%20%23%20%20(i.e.%20W24)%20to%20actual%20date%20for%20all%20reports

Jennifer Twyford Not planned

Please sign in to leave a comment.

4 comments

1
Avatar

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.

Roland Pumputis 1 vote
Comment actions Permalink
0
Avatar

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

Jennifer Twyford 0 votes
Comment actions Permalink
0
Avatar

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").

Sarah Ottey 0 votes
Comment actions Permalink
0
Avatar

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.

Roland Pumputis 0 votes
Comment actions Permalink