converting excel date into mm/dd/yyyy format

Question

I am reading from an excel sheet using poi jar where in there is a date column.If i print out the value of the data cloumn :

cell.getDateCellValue()

It gives me value like 3124.0

So i tried with this code to convert to the mm/dd/yyyy format:

SimpleDateFormat sdf = new SimpleDateFormat("MM/DD/YYYY");
String  s =  sdf.format(cell.getDateCellValue());
System.out.println(s);

But for reading a date 06/30/2001 i got this value as output:

06/181/2001

If excel sheet is in your control, please change excel content to text format(Right Click on the content -> Format Cells --> Text). And java code will see same content that was there in Excel. — Venkatesh Achanta, Jul 07 '15 at 10:39
That "value" is the number of days since January, 00th 1900. — Mr. Polywhirl, Jul 07 '15 at 10:42
I suggest you to look at the [SimpleDateFormat class reference](http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html) So you can easily edit your code — IlGala, Jul 07 '15 at 10:43
Your cell value for the date should be `37072.0`. Also, please read up on the valid Java date format characters. — Mr. Polywhirl, Jul 07 '15 at 10:47
Did you try using [DataFormatter from Apache POI](http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html)? — Gagravarr, Jul 07 '15 at 15:00

score 3 · Answer 1 · edited May 23 '17 at 12:00

First check your date format

  SimpleDateFormat sdf = new SimpleDateFormat("MM/DD/YYYY");

it should be

 SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

and also check the POI API For more info to deal with dates in excel,there are better ways to handle the dates.

Refer HSSFDateUtil

check HSSFDateUtil.isCellDateFormatted() , getExcelDate ,getJavaDate

getJavaDate

public static java.util.Date getJavaDate(double date)

Given an Excel date with using 1900 date windowing, and converts it to a java.util.Date.

NOTE: If the default TimeZone in Java uses Daylight Saving Time then the conversion back to an Excel date may not give the same value, that is the comparison excelDate == getExcelDate(getJavaDate(excelDate,false)) is not always true. For example if default timezone is Europe/Copenhagen, on 2004-03-28 the minute after 01:59 CET is 03:00 CEST, if the excel date represents a time between 02:00 and 03:00 then it is converted to past 03:00 summer time Parameters:

date - The Excel date. Returns: Java representation of the date, or null if date is not a valid Excel date

See Also: TimeZone

also refer Reading date values from excel cell using POI HSSF API

This is incorrect. It should NOT be `mm/dd/yyyy` because `mm` means minutes. — Dawood ibn Kareem, Jul 07 '15 at 10:45

score 3 · Accepted Answer · answered Jul 07 '15 at 10:41

3

Your Date Format String is wrong. Should be like following

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

Read the Java doc for SimpleDateFormat

answered Jul 07 '15 at 10:41

shazin

21,379
3
54
71

These classes are now legacy, supplanted by the modern *java.time* classes defined in JSR 310. – Basil Bourque Jun 14 '21 at 21:05

score 2 · Answer 3 · answered Jul 07 '15 at 10:41

2

Check out the JavaDoc for java.text.SimpleDateFormat:

D is for Day in year

Y is for Week in year.

You propably want to use the lowercase variants for d Day in Month and y Year

answered Jul 07 '15 at 10:41

hinneLinks

3,673
26
40

score 2 · Answer 4 · answered Jul 07 '15 at 10:41

2

You want to write "MM/dd/yyyy" as your date format. Capital DD means day of year, which for 30 June is 181.

I strongly recommend reading the JavaDoc for the SimpleDateFormat class.

answered Jul 07 '15 at 10:41

Dawood ibn Kareem

77,785
15
98
110

score 0 · Answer 5 · answered Jun 14 '21 at 16:19

Date is actually stored as number in a cell.

CellType cellType = cell.getCellType();
if (cellType == CellType.NUMERIC) {
     double numberValue = cell.getNumericCellValue();
 
     if (DateUtil.isCellDateFormatted(cell)) {
         Date javaDate= DateUtil.getJavaDate(numberValue);
         //use DateFormat or as it is
         myObj.setValueFromCell(javaDate);
      } else {
         // Use BigDecimal to avoid  Scientific numbers like 1.2345678E9
         String stringCellValue =  BigDecimal.valueOf(numberValue).toPlainString();
         myObj.setValue(stringCellValue);
    }
}

converting excel date into mm/dd/yyyy format

5 Answers5