Applying Date Format as mmm-yy

[Siva72]

Hi

I have an excel template(attached) with C6:N6 as Custom Date Format set to mmm-yy.
Using Aspose.cells, populating the value in those cells as below.
for (Int32 inc = 0; inc < 12; inc++) {
Range range0 = cells.CreateRange(collbl2[inc] + (initoff - 1));
range0.Value = (inc + 1) + “/1/” + DateTime.Now.Year;
}

After generating the excel, if i open and see the columns, they are showing as 01/01/2022, 02/01/2022…Formatting didnt apply. If i just double click the column, then the formatting applies automatically.

Could you please let us know how to fix this.

Thanks
Siva PoreddyEISAppRec.zip (19.6 KB)

[Amjad_Sahi]

@Siva72,

Thanks for the sample file.

DateTime values are stored/inserted as numeric notation in MS Excel. I checked those values in the range of cells (C6:N6) and these are inserted as string/text instead of numeric values. So, you cannot apply formatting to the cells. You need to convert those values as numeric notations instead of string. When you double click in the cell, MS Excel just converts to numeric values. For your needs, you may convert to values to numeric values while inserting your DateTime data. See the updated sample code that you may try:
e.g.
Sample code:

for (Int32 inc = 0; inc < 12; inc++)
{
    Range range0 = cells.CreateRange(collbl2[inc] + (initoff - 1));
    range0.PutValue((inc + 1) + "/ 1 /" + DateTime.Now.Year, true, true);
} 

the second Boolean parameter (“true”) of PutValue method overload does convert to appropriate data type.

[Siva72]

Thanks Amjad. The above didnt work.
I made the below change and it worked. Thanks.

for (Int32 inc = 0; inc < 12; inc++) {
Range range0 = cells.CreateRange(collbl2[inc] + (initoff - 1));
object myDate = (inc + 1) + “/1/” + DateTime.Now.Year;
DateTime myDateFormat = Convert.ToDateTime(myDate);
range0.Value = myDateFormat;
//xlwkst.get_Range(collbl2[inc] + (initoff - 1)).Value = (inc + 1) + “/1/” + DateTime.Now.Year;
}

[Amjad_Sahi]

@Siva72,

Good to know that your issue is sorted out now. Feel free to contact us any time if you have further queries or issue, we will be happy to assist you soon.