Date Format/Conversion
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I'm currently trying to calculate trailing 12 month financials and our financial data is coming through as a string in the following format "mmYYYY"
Therefore it doesn't sort properly.
I'm having difficulties getting the date to convert to an actual date .
Thanks!
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I would suggest the following steps... 1) Convert your MonthYear field to string if isn't already stored that way, 2) use the following function to get your data in the right starting format to correct for single digit months: "padleft([MonthYear],6,'0')" and finally 3) The function "DateTimeParse([MonthYear],'%m%Y')" will return you a properly formatted date data for the first of the given month which should allow you to properly sort.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
IIF(length(toString([MonthYear]))<6,DateTimeParse('01'+PadLeft(toString([MonthYear]),6,'0'),'%d%m%Y'),DateTimeParse('01'+toString([MonthYear]),'%d%m%Y'))
