Ok, so I've been trying to figure this out and hopefully someone else can shed some light on this. I have a formula field, that looks at a few different dates and returns the latest or furthest out one. The context is a project record with multiple milestones (sub-items in formula) and change requests. The project record date should reflect the latest, or furthest out, date for the milestone or change request. Getting the latest date from either isn't the issue, using the following formula:
MAX([Sub-Items].[Date Range].[Date Range End],[Change Requests].[Sub-Items].[Date Range].[Date Range End])
This returns a date, which looks similar in cell placement to other dates, in the formula field and is treated as a date in the Timeline View, Calendar View, and Automations (more on dates in automations, related to this, in a separate question). The date seems to be auto formatted to "MMM D" when in the current year and "MMM D, YYYY" when not in the current year. Given that some want to see it with the year at any time, this is where formatting comes in and the problem happens.
The problem is when I try to format the date using the formula:
DATE_FORMAT(MAX([Sub-Items].[Date Range].[Date Range End],[Change Requests].[Sub-Items].[Date Range].[Date Range End]),"standard")
This returns what looks like a date string, based on cell placement. It's also treated differently in Automations and I can't seem to select it in Timeline or Calendar view. This further leads me to believe the "DATE_FORMAT" (and "DATETIME_FORMAT") functions return a date string and not a date value.
Is anyone else having an issue with this and if so, what are some tips? My current thought (which I don't like) is to have two fields, one for viewing the date in Grid view or expanded record view and another for use in the Timeline and Calendar view. I really don't like that workaround and would like one field that can return a formatted date value.
Thanks for the help.