Making chart from custom data format in Excel 2010

Question

I am defining custom data format via Apache POI. I know I need to use following construction:

DataFormat data = this.workbook.createDataFormat();
cellStyle.setDataFormat(data.getFormat("YY:DDD:HH:MM:SS"));

I found a reference in Apache POI documentation about the formats.

The data i need to format represents date and time i.e.:

00:000:00:15:00 
YY:DDD:HH:MM:SS

So far it works all right. Right click on Excel cell, checking cell format -> custom yy:ddd:hh:mm:ss. However I am not able to make a chart out of this custom time format. Selecting this data:

task   | time
-------|----------------
task 1 | 00:000:00:15:00
task 2 | 00:000:00:20:00
task 3 | 00:000:00:10:00

and choosing option insert bar chart outputs some gibberish. Is there a way to make the chart work, without creating additional data(like counting total seconds and displaying the seconds values)?

Answer 1

Excel deals with dates and times as serial numbers where the number is the days since Jan 0, 1900, and the decimal is a fractional part of a 24 hour day (for more info, see CPearson's Excel Date/Time. So in Excel, 10/12/2012 10:20:07 = 41194.4306392361. To help us interact with Excel, it recognizes dates/times in certain standard formats (like the traditional, US MM/DD/YYYY HH:MM:SS). The problem you seem to be running into is that your date format (YY:DDD:HH:MM:SS) isn't recognized by Excel as a date/time. You have two solutions:

1. Export your date from Apache in an "Excel friendly" format; or
2. Convert your custom format into an "Excel friendly" format in Excel using formulas.

If you use #2, and DDD in your custom date format is literally the day of the year (e.g 10/12/2012 is the 286th day of the year), you'll likely need VBA to handle converting that to the appropriate day/month combination.