Expected result of ExcelCell.GetFormattedValue for DateTimes

GemBox.Spreadsheet
1

Example:
Create a new excel file, and into the top left most cell, enter: 5/5/2022 11:58:59

image

Then run code:

            ExcelFile wb = ExcelFile.Load(yourExcelFileName, LoadOptions.XlsxDefault);
            ExcelWorksheet ws = wb.Worksheets[0];
            string str = ws.Cells[0, 0].GetFormattedValue();

str becomes: “5/05/2022 11:58:59 AM”

Is this working as designed? I was kind of expecting “5/05/2022 11:58” to be returned, because that’s what you can see in the A1 cell in Excel. Whereas “5/05/2022 11:58:59 AM” is what you can see in Excel in the formula entry bar, when cell A1 is selected.

The format string I can see that Excel has automatically derived for this cells content is:
“d/mm/yyyy h:mm” (my PC is configured with Australian regional settings, where day is typically displayed before the month).

I realise I can use ExcelCell.DateTimeValue, and format the date as required, but was more curious than anything.

2

Hi,

I was unable to reproduce this issue.

When I write that value into a cell, I get this formatting:
date-number-format

And I get the correct result when using ExcelCell.GetFormattedValue.

I also tried using the “d/mm/yyyy h:mm” number format, but the issue doesn’t occur with it as well.
Just in case, what version of GemBox.Spreadsheet are you using?

Regards,
Mario

3

I am using hotfix: 49.0.1174 (file version 40.0.35.1174).Running in .NET Framework 4.7.2.
Pretty sure I recall seeing this behavior with earlier versions as well.

My PCs regional settings in Windows 10 looks like this:

image
image895×736 83.9 KB

I’ll try a few things to do with my PC settings. May also try a non hotfix version.

4

The format for the cell looks like this in Excel:

image
image779×547 23.6 KB

Interestingly, if I change the format in Excel to custom format:
d/mm/yyyy hh:mm AM/PM
then str becomes: 5/05/2022 11:58 AM

If custom format = d/mm/yyyy h:mm
then str becomes “5/05/2022 11:58:59 AM”

If custom format = d/mm/yyyy hh:mm
then str becomes “5/05/2022 11:58”

5

Hi,

Please try again with this bugfix:
https://www.gemboxsoftware.com/spreadsheet/nightlybuilds/GBS49v1201.zip

Or this NuGet package:
Install-Package GemBox.Spreadsheet -Version 49.0.1201-hotfix

Does this solve your issue?

Regards,
Mario

6

Hi Mario,
Yes, that solves my issue.
Thanks.