I have a pivot table created using EPPlus. The data is basic sales information, with a date, item name and total.
The Pivot table contains the name and date in the row with total summed. It also groups by date so can drill into sales by date.
This all works fine, the problem being the date grouping gets sorted as string value and not date. The dates are stored as Excel dates (so internally as numbers).
When spreadsheet is opened in Excel the date group sorting will not change even if pivot table is refreshed or sort order changed.
The only way to force sort change is to:

1. Click into any date in the Pivot table
2. Right mouse click and select Group
3. Select default group options and select OK.
   Have done fair bit of searching, there are a few similar entries but over 5+ years and no response. Some suggestions include setting the PivotTable refreshOnLoad attribute which in theory should refresh. Doesn’t do anything:
   pivotTable.CacheDefinition.CacheDefinitionXml.DocumentElement?.SetAttribute(
   “refreshOnLoad”, 1.ToString());

Below is example code that will build a new spreadsheet, create data and build the pivot table with date grouping. Upon opening you will see the grouped date columns are sorted as text.

I am using recent EPPlus Core package and opening with whatever the most recent Office 365 Excel version is.

    [TestMethod]
    public void BuildPivotTable()
    {
        //build spreadsheet data and build pivot table with date grouping. 
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        Random random = new Random();
        string[] widgetNames = { "Widget 1", "Widget 2", "Widget 3" };

        var fileName = $"Pivottest {DateTime.Now.ToString($"yyyyMMddhhmmss")}.xlsx";

        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Data");

            worksheet.Cells[1, 1].Value = "Name";
            worksheet.Cells[1, 2].Value = "TransactionDate";
            worksheet.Cells[1, 3].Value = "Total";
           //build 50 rows of data. 
            for (int i = 2; i < 51; i++)
            {
                worksheet.Cells[i, 1].Value = widgetNames[random.Next(widgetNames.Length)];

                DateTime transactionDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, random.Next(1, 29));

                var cell = worksheet.Cells[i, 2];
                cell.Value = transactionDate;
                cell.Style.Numberformat.Format = "mm-dd-yy";
                
                worksheet.Cells[i, 2].Value = transactionDate;
                worksheet.Cells[i, 3].Value = (decimal)(random.Next(10, 41) + random.NextDouble()); ;
            }

            var dataRange = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column];
            ExcelWorksheet pivotSheet = package.Workbook.Worksheets.Add("PivotTable");
            var pivotTable = pivotSheet.PivotTables.Add(pivotSheet.Cells["A1"], dataRange, "PivotTable");

            pivotTable.RowFields.Add(pivotTable.Fields["TransactionDate"]);
            pivotTable.RowFields.Add(pivotTable.Fields["Name"]);
            pivotTable.DataFields.Add(pivotTable.Fields["Total"]).Function = DataFieldFunctions.Sum;

            var transactionDateField = pivotTable.Fields["TransactionDate"];
            transactionDateField.AddDateGrouping(eDateGroupBy.Days);


            //try to force pivot table refresh when open?
            pivotTable.CacheDefinition.CacheDefinitionXml.DocumentElement?.SetAttribute(
                        "refreshOnLoad", 1.ToString());
            FileInfo file = new FileInfo(fileName);
            package.SaveAs(file);

        }

    }