Reports "By" date summarized by Month: How to adjust the date within the CSV export

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.

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’.