topic Re: Date exporting to excel in a timestamp? in QlikView

Date exporting to excel in a timestamp?

NickHoff — Mon, 26 Feb 2018 15:04:54 GMT

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')))

Re: Date exporting to excel in a timestamp?

sunny_talwar — Mon, 26 Feb 2018 15:07:18 GMT

Try this

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

or this

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

Re: Date exporting to excel in a timestamp?

NickHoff — Mon, 26 Feb 2018 15:24:35 GMT

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.

Re: Date exporting to excel in a timestamp?

sunny_talwar — Mon, 26 Feb 2018 15:28:03 GMT

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)))