Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)?

share|improve this question
    
this does not seem right - a cutsom format in Excel with YY:DDD should resolve into something like 12:Fr today, because excel would interpret this as year and day. Have you tried to really write something in a cell with that format? –  Jook Oct 12 '12 at 12:34
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
I'm not sure about the source for your "correction", but according to MS, it does begin in Jan 1900. Please see support.microsoft.com/kb/214094. –  dav Sep 22 '13 at 1:44
    
You are indeed right. That Microsoft resource actually has some good information altogether, so it could be a good idea to edit that into your answer. –  Doktoro Reichard Sep 22 '13 at 10:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.