Expand my Community achievements bar.

Display Month or Quarter but not year in a report

Avatar

Level 2
In an Hours reports we are trying to display just the Month, in which an hours were entered, in one column and just the Quarter in another column of a report. The issue we are having is the year is showing as well. valueformat=atDateAsMonthString displays "Jun, 2017" valueformat=atDateAsQuarterString displays "Q2, 2017" We are trying to get WF to drop the comma and the year for both. Any suggestions?
5 Replies

Avatar

Level 10
I used the following text mode code to drop the year and only show MM/DD for Planned Start Date - perhaps you can tweak to accomplish what you're trying to achieve: valueexpression=CONCAT(MONTH({plannedStartDate}),"/",DAYOFMONTH({plannedStartDate}))

Avatar

Level 2
This helps a lot Katherine. If I wanted to get the actual month abbreviation, such as Aug, instead of the number, such as 8, which represents the Month do you know how I would do that? Based on what you showed me I now have: valueexpression=MONTH({entryDate}) valueformat=HTML Thanks!

Avatar

Level 3
Hi John, Unfortunately, it gets a little ugly from here. :-) To display month only, try this formula: valueexpression=IF(MONTH({entryDate})=1,"Jan", IF(MONTH({entryDate})=2,"Feb", IF(MONTH({entryDate})=3,"Mar", IF(MONTH({entryDate})=4,"Apr", IF(MONTH({entryDate})=5,"May", IF(MONTH({entryDate})=6,"Jun", IF(MONTH({entryDate})=7,"Jul", IF(MONTH({entryDate})=8,"Aug", IF(MONTH({entryDate})=9,"Sep", IF(MONTH({entryDate})=10,"Oct", IF(MONTH({entryDate})=11,"Nov", IF(MONTH({entryDate})=12,"Dec","")))))))))))) Quarter only is a little less daunting, and this should work: valueexpression=IF(MONTH({entryDate})<=3,"Q1", IF(MONTH({entryDate})<=6,"Q2", IF(MONTH({entryDate})<=9,"Q3", IF(MONTH({entryDate})<=12,"Q4","")))) As a side note, I have seen some weirdness where when you add a date field to a report using the field chooser, you may see something slightly different than when you put the date in a calculation. From what I can tell, this seems to be due to the “field chooser version” adjusting properly for your time zone, whereas the calculation does not. I only mention this because one of those was staring me in the face when I did a quick test before posting this! (e.g., my 3/31/2016 showed up as “Apr”) Hopefully this gets you a little closer. Kathy

Avatar

Level 2
Thank you Kathy. I was thinking I needed to go that route and was dreading it. But at least you confirming it is one of my only options. Whenever I add in any complex logic the team has some unforeseen request to display the information in another format that leaves the logic string useless. But that is the life of an WF Admin. Cheers, John

Avatar

Level 10
LOL, it sure is! In fact, I worked out the logic, then the user said 'nah, keep the year' so removed it - only to have to put it back in a few weeks later!