Getting Time Format of Cell Value

Nov 1, 2012 at 12:46 PM

Hello, I am grabbing a selected range of data from a worksheet and storing it in an object array in my application using the following code:

ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
var range = selection.GetValue();
range = (object[,])range;

This works fine and I get the cell values loaded into the object array - however I have noticed cell values such as "12:00", "12:01", "12:02" etc. representing times are being stored in the object array as numeric values such as 0.5, 0.50069444444444444, 0.50138888888888888.

I understand this is due to the fact that Excel has no native type for DateTime so these values are simply stored as doubles by excel and then associated format applied to those values in the worksheet. 

My issue is I need to be able to detect these types of values and handle them accordingly in my application.  Is there a way I can examine the number format of a cell or some other way to get the real cell value rather than the stored numeric one?

Thanks in advance for any help with this.

Nov 2, 2012 at 4:44 AM

Hi Mark, 

The GET.CELL function with option 7 gives you the number format of the cell, and option 53 allows you to get the string as displayed in the cell. 

So this should work: 

    string format = (string)XlCall.Excel(XlCall.xlfGetCell, 7, selection); 
    string text   = (string)XlCall.Excel(XlCall.xlfGetCell, 53, selection); 

If this is called in a UDF context, the UDF must be marked as [ExcelFunction(IsMacroType=true)]. 

All the GET.XXX information functions are explored in the Distribution\Samples\GetInfoAddIn.dna and matching GetInfoSample.xls.