Date Format in Resulting Excel Report

[KevinP]

We have an emailed report with users' start date which of course comes via Excel instead of a CSV abd it's interpreting the date as a string of irrelevant numbers.  Only if I open the Excel sheet, change the cell format to Date, does it reflect the date correctly.  In Data Analyzer and while building the report, the date looks perfect; only when its exported to an Excel attachment does it show up wrong.  

So I've been trying to play with DATE_FORMAT and CAST or CONVERT (to a string) to get it to spit out the correct date format to Excel....but I've spent a lot of time doing trial and error.  Do you have a proper method to do this?  

I currently have it set like this in my select statement: DATE_FORMAT(u1.startdate, "%m-%d-%Y")
Which looks fine in Data Analyzer:

But looks like this in Excel:

I've been playing with different formats, and the closest I got was using this:
DATE_FORMAT(u1.startdate, "%m-%d-%Yy")
Which comes out like this in Excel with that extra 'y' in it of course...soon as I take the extra y away it goes back to irrelevant numbers.

This is probably something really simple for someone who does this every day and I'm sure I'm missing one tiny piece that I don't understand.  Any pointers on the proper date format to get this to show up correctly/natively in Excel?

Thanks,

Kevin

[SumathiSomala]

Try 

DATE_FORMAT(u1.startdate, '%m-%d-%Y')

[KevinP]

@SumathiSomala 
Nope - whether I use single or double quotes in DATE_FORMAT(u1.startdate, '%m-%d-%Y'), the result is the same.

[Vinit556]

Can u please try:

FROM_UNIXTIME(u1.startdate,  '%Y %D %M %h:%i:%s %x')

[KevinP]

@Vinit556It doesn't appear to like that one, I put it in for datestart:
FROM_UNIXTIME(u1.startdate, '%Y %D %M %h:%i:%s %x') AS DATESTART

Which resulted in the below:

[rushikeshvartak]

DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') 

[KevinP]

@rushikeshvartak 

Negatory - that made the start date a little weirder.  Here is the what's in the select statement and the output: DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') AS DATESTART,

[KevinP]

Nobody?  I can't be the first person who just wanted to return a date into an Excel-ready format?

[rushikeshvartak]

Can you share your query?

Since it is functioning for me as planned

[Dhruv_S]

Hi @KevinP 

Could you please share the complete query and where you are using it. It can be an issue with default date format in your excel since it is working for you as expected in the data analyzer. Can you please share the complete steps you have followed so that we can replicate this.

Regards,

Dhruv Sharma

[KevinP]

@Dhruv_S 

Here is the full query - you can see I've been messing with the format of the starting date and end date trying a couple of different things now as a result of this forum.

Thanks!

select u1.employeetype, u1.firstname, u1.lastname, u1.email, u1.SECONDARYEMAIL, case u1.statuskey when '1' THEN 'Active' WHEN '0' THEN 'Inactive' END AS userStatus, u1.JOB_FUNCTION as team, u1.DEPARTMENTNAME, DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s') AS DATESTART, DATE_FORMAT(u1.enddate, '%m-%d-%Y') AS DATEEND, u1.CUSTOMPROPERTY44 as company, u2.firstname as 'manager_firstname', u2.lastname as 'manager_lastname', u1.owner as 'manager_email' from users u1, users u2 where u1.employeetype = 'Contractor' and u1.statuskey=1 and u1.manager = u2.userkey and u1.firstname != 'Smoke' and u1.firstname != 'T1' and u1.firstname != 'reserve'

[rushikeshvartak]

Can you check configuration in global configuration -> Preference - Date Format

[Dhruv_S]

Hi @KevinP 

Could you please refer this post and let us know if this helps.

Solved: formatting date in email template - Saviynt Forums - 64336

Regards,

Dhruv Sharma

[KevinP]

@Dhruv_S @rushikeshvartak 
I went to Global Settings -> Preferences and the Date/Time setting was blank so I set it to MM-DD-YYYY, though that doesn't seem to make a difference to the emailed report.  

I also viewed the post that Dhruv mentioned, but it's the same thing that I've seen before where we are told to not have the column name end in 'date' which I've already been doing. You can see my full query now where I'm trying a bunch of different formats to see how they all come out.

Again - I'm not quite sure why DATE_FORMAT(u1.startdate, "%m-%d-%Y") doesn't work, while DATE_FORMAT(u1.startdate, "%m-%d-%Yy") gets me most of the way there with the extra 'y'.


select u1.employeetype, u1.firstname, u1.lastname, u1.email, u1.SECONDARYEMAIL, case u1.statuskey when '1' THEN 'Active' WHEN '0' THEN 'Inactive' END AS userStatus, u1.JOB_FUNCTION as team, u1.DEPARTMENTNAME, u1.startdate as DateStart1, DATE_FORMAT(u1.startdate, '%Y-%m-%d %H:%i:%s') AS DATESTART2, DATE_FORMAT(u1.startdate, '%m-%d-%Y') AS DATEStart3, DATE_FORMAT(u1.startdate, "%b %d, %Y") AS DateStart4, DATE_FORMAT(u1.startdate, "%m-%d-%Y") AS DateStart5, DATE_FORMAT(u1.startdate, "%m-%d-%Yy") AS DateStart6, DATE_FORMAT(u1.startdate, "MM-DD-YYYY") AS DateStart7, u1.CUSTOMPROPERTY44 as company, u2.firstname as 'manager_firstname', u2.lastname as 'manager_lastname', u1.owner as 'manager_email' from users u1, users u2 where u1.employeetype = 'Contractor' and u1.statuskey=1 and u1.manager = u2.userkey and u1.firstname != 'Smoke' and u1.firstname != 'T1' and u1.firstname != 'reserve'

[Dhruv_S]

Hi @KevinP 

Thanks for your patience and co-operation on this issue.  

We have tested the same query in our environment and getting results as expected while trying to use the query in runtime analytics and downloading as excel report. The report gets downloaded as Excel 97-2003 Worksheet (attached sample here).

The results look like below in the report we have downloaded post replicating the above query.

DATESTART1	DATESTART2	DATESTART3	DATESTART4	DATESTART5	DATESTART6	DATESTART7
08-24-2023 00:00:00	2023-08-24 00:00:00	08-24-2023	Aug 24, 2023	08-24-2023	08-24-2023y	MM-DD-YYYY
09-05-2023 00:00:00	2023-09-05 00:00:00	09-05-2023	Sep 05, 2023	09-05-2023	09-05-2023y	MM-DD-YYYY
09-22-2023 00:00:00	2023-09-22 00:00:00	09-22-2023	Sep 22, 2023	09-22-2023	09-22-2023y	MM-DD-YYYY
09-29-2023 00:00:00	2023-09-29 00:00:00	09-29-2023	Sep 29, 2023	09-29-2023	09-29-2023y	MM-DD-YYYY
11-23-2023 00:00:00	2023-11-23 00:00:00	11-23-2023	Nov 23, 2023	11-23-2023	11-23-2023y	MM-DD-YYYY
11-23-2023 00:00:00	2023-11-23 00:00:00	11-23-2023	Nov 23, 2023	11-23-2023	11-23-2023y	MM-DD-YYYY

As confirmed by Engineering teams internally there has been no recent changes/enhancements related to the date formatting in any of the recent versions. 

Could you please check with your Microsoft excel configurations if there is any setting which might be causing the date to appear in wrong format when you open the excel.

Regards,

Dhruv Sharma

[KevinP]

Dhuv,

What's interesting is mine come as .xlsx (post-Excel 2003), not .xls like yours.  Where is that controlled?  Is there a setting somewhere to define the file extension/format it gets delivered as?

[rushikeshvartak]

select u1.employeetype, u1.firstname, u1.lastname, u1.email, u1.SECONDARYEMAIL, case u1.statuskey when '1' THEN 'Active' WHEN '0' THEN 'Inactive' END AS userStatus, u1.JOB_FUNCTION as team, u1.DEPARTMENTNAME, u1.startdate as DateStart1, DATE_FORMAT(u1.startdate, '%Y-%m-%d %H:%i:%s') AS DATESTART2, DATE_FORMAT(u1.startdate, '%m-%d-%Y') AS DATEStart3, DATE_FORMAT(u1.startdate, "%b %d, %Y") AS DateStart4, DATE_FORMAT(u1.startdate, "%m-%d-%Y") AS DateStart5, DATE_FORMAT(u1.startdate, "%m-%d-%Y ") AS DateStart6, DATE_FORMAT(u1.startdate, "MM-DD-YYYY") AS DateStart7, u1.CUSTOMPROPERTY44 as company, u2.firstname as 'manager_firstname', u2.lastname as 'manager_lastname', u1.owner as 'manager_email' from users u1, users u2 where u1.employeetype = 'Contractor' and u1.statuskey=1 and u1.manager = u2.userkey and u1.firstname != 'Smoke' and u1.firstname != 'T1' and u1.firstname != 'reserve'

[rushikeshvartak]

@KevinP  Did you tried above query