Could you add a field to the group() function that will deal specifically with dates? When I convert a list of dates from "M/d/yyyy" to "MMM yyyy" and then group() them, they are always returned in alphabetical order. I have to jump through hoops to get them into chronological order. If there were an additional parameter in group(), like there is with sort(), that would help me out an awful lot.
--Trudy
Hey Trudy, Short answer is that when converting dates (in the formula), you should almost always convert into a numerical format for exactly the reason you call out. When you group Dates, MMM(M) will get sorted alphabetically which isn't what you want, whereas M(M) will work well. Further, you'll want to convert them to a format with the year first. I would convert M/d/yyyy into yyyyMMdd. If the months are at the start of the date string, 01/01/2017 will get sorted before 02/02/2016. Once you've converted in the formula, use the properties menu (to change how the dates look to the users.
Longer (and better) answer, is that we've just rolled out some great new functionality on this that can hopefully help out. Quick screen-cap here: https://drive.google.com/file/d/0B8QKk_htEW7WZmx6LU5TSVE1czg/view?usp=sharing.
This allows you to do all of the work (conversion, grouping, etc.) in the properties menu and not in the formula.
If this doesn't work for what you're doing right now, let me know and we're happy to help!
OK thanks Zach! I think I was making a lot more work for myself. I'll work on it.
--Trudy