Is there an OpenXml (C#) date/time format for "m/d/yyyy h:mm tt" [AM/PM]?

Question

Looking at the list here, you can see:

• 0 - General
• 1 - 0
• 2 - 0.00
• 3 - #,##0
• 4 - #,##0.00
• 9 - 0%
• 10 - 0.00%
• 11 - 0.00E+00
• 12 - # ?/?
• 13 - # ??/??
• 14 - d/m/yyyy
• 15 - d-mmm-yy
• 16 - d-mmm
• 17 - mmm-yy
• 18 - h:mm tt
• 19 - h:mm:ss tt
• 20 - H:mm
• 21 - H:mm:ss
• 22 - m/d/yyyy H:mm
• 37 - #,##0 ;(#,##0)
• 38 - #,##0 ;Red
• 39 - #,##0.00;(#,##0.00)
• 40 - #,##0.00;Red
• 45 - mm:ss
• 46 - [h]:mm:ss
• 47 - mmss.0
• 48 - ##0.0E+0
• 49 - @

You can see most cases seem to be covered, 22 gets very close, but what I really need is m/d/yyyy h:mm tt - does anyone know of a way to set that in OpenXml? Thank you.

Answer 1

There isn't a built in format to achieve what you're after within OpenXml but you can easily add your own format. The format string you need is m/d/yyyy\ h:mm\ AM/PM.

In order to apply a format you need to create a NumberingFormats object, add a NumberingFormat to it and assign it to the NumberingFormats property on a Stylesheet. The following method will create a Stylesheet with the m/d/yyyy\ h:mm\ AM/PM format:

private static Stylesheet CreateStyles()
{
    Stylesheet styleSheet = new Stylesheet();

    NumberingFormats nfs = new NumberingFormats();

    NumberingFormat nf;
    nf = new NumberingFormat();
    nf.NumberFormatId = 165;
    nf.FormatCode = "m/d/yyyy\\ h:mm\\ AM/PM";
    nfs.Append(nf);

    CellFormat cf = new CellFormat();
    cf.NumberFormatId = nf.NumberFormatId;
    cf.ApplyNumberFormat = true;

    CellFormats cfs = new CellFormats();
    cfs.Append(cf);
    styleSheet.CellFormats = cfs;
    styleSheet.NumberingFormats = nfs;
    styleSheet.Borders = new Borders();
    styleSheet.Borders.Append(new Border());
    styleSheet.Fills = new Fills();
    styleSheet.Fills.Append(new Fill());
    styleSheet.Fonts = new Fonts();
    styleSheet.Fonts.Append(new Font());

    CellStyles css = new CellStyles();
    CellStyle cs = new CellStyle();
    cs.FormatId = 0;
    cs.BuiltinId = 0;
    css.Append(cs);
    css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
    styleSheet.Append(css);
    return styleSheet;
}

The following code will create a new file from scratch with 2 dates (in A1 and B1) using the format added above:

public static void CreateFile(string filename)
{
    using (SpreadsheetDocument spreadsheetDocument = 
        SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
    {
        // Add a WorkbookPart to the document.
        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();
        workbookpart.AddNewPart<WorkbookStylesPart>();

        // Add a WorksheetPart to the WorkbookPart.
        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        // Add Sheets to the Workbook.
        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        // Append a new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet()
        {
            Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1,
            Name = "Sheet 1"
        };
        sheets.Append(sheet);
        Worksheet worksheet = new Worksheet();
        SheetData sheetData = new SheetData();

        Stylesheet styleSheet = CreateStyles();
        Row row = CreateRow();

        sheetData.Append(row);
        worksheet.Append(sheetData);
        worksheetPart.Worksheet = worksheet;
        workbookpart.WorkbookStylesPart.Stylesheet = styleSheet;

        // Close the document.
        spreadsheetDocument.Close();
    }
}

private static Row CreateRow()
{
    Row row = new Row();

    DateTime now = DateTime.UtcNow;
    //add a date cell using the number data type
    Cell cell = new Cell();
    cell.StyleIndex = 0;
    cell.DataType = CellValues.Number;
    string columnValue = now.ToOADate().ToString();
    cell.CellValue = new CellValue(columnValue);
    row.Append(cell);

    //add a date cell using the date data type
    Cell cell2 = new Cell();
    cell2.StyleIndex = 0;
    cell2.DataType = CellValues.Date;
    columnValue = now.ToString("o");
    cell2.CellValue = new CellValue(columnValue);
    row.Append(cell2);
    return row;
}

Original answer (based on the 24hr time with the AM/PM specifier)

Unfortunately I don't think this is possible.

You can define custom formats but section 18.8.31 numFmts of the spec states:

If the format contains AM or PM, the hour is based on the 12-hour clock, where "AM" or "A" indicates times from midnight until noon and "PM" or "P" indicates times from noon until midnight. Otherwise, the hour is based on the 24-hour clock.

So, mixing the 24hr format with an AM/PM suffix isn't possible.