Skip to main content

When building a Report, and aggregating your data on a date field by Month, I note that the date format on-screen in Gainsight has a format of mmm-yy (ie, Feb-19 or Mar-19, representing February of 2019 or March of 2019). So far, so good.

 

However, if you export that Report, Excel interprets the date field as mmm-dd, so Feb-19 is actually interpreted as the 19th of February, and the year is assumed to be the current year of 2020. Thus, the date is interpreted as 19 Feb 2020 (or 2/19/20).

 

This date interpretation makes any Excel-based sorting, filtering or pivoting very difficult because the date Gainsight is intending to offer (2/1/2019, in February of 2019) is not the date Excel interprets (2/19/2020, in February of 2020.) 

 

If you attempt year-over-year analysis by Month, you end up with Feb-19 and Feb-20 within Gainsight. Once exported to Excel, however, the dates are re-interpreted as 2/19/2020 and 2/20/2020, and the analysis is extremely hard to conduct.

 

Has anyone else run into this, and developed a solution to counter it?

 

 

 

Is Excel automatically converting the column when you open the file?  Or are you doing a conversion on the column once the file is open?  When I export to excel and open the file, my mmm-yy column is of a type General and not Date.

@rob_culross - Thanks for the clarifying question. In my case, Excel is applying a type of General only to the Column header, and a type of Custom on all of the values within the column. That Custom type is a date.

When I see Feb-20 within Gainsight, it exports to 20-Feb, which Excel interprets as 43811, or 2/20/2020.

You’ve given me an idea, which is to look at the Date/Time settings on my entire Mac system, and try a few configurations to determine if I can ‘stop’ Excel from its interpretation.

There is a “simple” fix purely in Excel… 

Say you have your data looking like: 

where your date column is in a (for the sake of the formula)

In the next empty column type the following formula…

     =DATEVALUE(CONCAT("1-",A2))

The you can choose to format the column in whatever date format you see fit and you should be good to go.

Thanks @werickson . I’ve been trying to find a formula-based way to work around this too.

The value of the cell is a number (43811), to which Excel is applying Custom formatting to make it appear as 20-Feb. Any formula action I take is actioning on the underlying number, and not the date formatting. I tried your suggestion and a few variations on that too, and I always end up at a #VALUE error.

=DATEVALUE(CONCAT("1-",MONTH(A2),"-",DAY(A2)))

I believe DATEVALUE only works on text.  But if you take off DATEVALUE then that formula should work to give you a d-mm-yyyy format or whatever the cell is set to.

@Rob  datevalue only works on text, you are right.  The concat builds a text string of 1-MM-YY (where we put the day from the date in the year for the date), and that is converted back to a date by the date value because I wasn’t sure if @matthew_lind needed it in date format for analytics

 

Thanks for the input, @rob_culross and @werickson.

I think the central issue is that Excel (and maybe it’s my configuration of Excel, as Rob suggested) assigns the ‘wrong’ date serial number to the values I export from Gainsight.  Because I ‘start’ in the wrong place, I cannot find any formula which gets me to the right place.

Excel assumes the text ‘20-Feb’ refers to 2/20/2020, and assigns it serial number 43881, whereas for analysis, I’d like the serial number to reference 2/1/2020, or 43862 (probably a poor example, due to all the 20’s in the date)

Excel assumes the text ‘19-Feb’ refers to 2/19/2020, and assigns it serial number 43880, whereas for analysis, I’d like the serial number to reference 2/1/2019, or 43497.

This isn’t a huge deal….I’ve taken the slightly longer route of summarizing on ‘Week’, which generates a usable serial number for the date, and then using Excel’s Pivot Tables to re-summarize by ‘Month’.

 

Reply


Terms & ConditionsCookie settings