Date exporting to excel in a timestamp?

[NickHoff]

Hello,

I have a report I put together that doesn't have a time stamp in the data, but when I export it to excel the timestamp defaults to midnight and the date displays as the following day.  i.e. 1/31/2018 as 2/1/2018.  If i click the cell the timestamp goes away and the date displays as 1/31/2018.

I'm formatting my date as the following to get the last day of the month, i even tried to force the MM/DD/YYYY and nothing seems to work.:

the expression for the last day of the month:

MAX(Date(MonthEnd(Date(CalendarDate)),'MM/DD/YYYY')))

[sunny_talwar · Accepted solution]

Try this

Max(Date(Floor(MonthEnd(CalendarDate)), 'MM/DD/YYYY'))

or this

Max(DayName(MonthEnd(CalendarDate), 'MM/DD/YYYY'))

[NickHoff]

Adding Floor worked, the DayName broke the function.  Thanks for the quick response Sunny.  I didn't know you could add Floor to a date.  Great learning experience.

[sunny_talwar]

The problem was MonthEnd function which is usually a timestamp to the last second for the day... for example, MonthEnd(Today()) will be 02/28/2018 23:59:59. In order to change that into just the date, we use Floor() function. The function with DayName would look like this (Basically, remove the formatting)

Max(DayName(MonthEnd(CalendarDate)))