Converting number representing a date in Excel to a Java Date object
Here is a minimal working example how to convert an Excel date to a Java date:
Date javaDate= DateUtil.getJavaDate((double) 41275.00);
System.out.println(new SimpleDateFormat("MM/dd/yyyy").format(javaDate));
which returns
01/01/2013
You also need to import the following packages:
java.text.SimpleDateFormat
java.util.Date
Apache POI has some utilities for that http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html, notably http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html#getJavaDate(double)
Note Excel stores dates as the number of days (plus fractional days) since 1900 (and in some cases it can be from 1904). See http://support.microsoft.com/kb/180162.
tl;dr
Use modern java.time classes.
LocalDate // Represent a date-only vaule, without time-of-day and without time zone.
.of( 1899 , Month.DECEMBER , 30 ) // Specify epoch reference date used by *some* versions of Excel. Beware: Some versions use a 1904 epoch reference. Returns a `LocalDate` object.
.plusDays( // Add a number of days.
(long) Double.parseDouble( "41275.00" ) // Get a number of whole days from your input string.
) // Returns another instance of a `LocalDate`, per Immutable Objects pattern, rather than altering the original.
.toString() // Generate text representing the value of this `LocalDate` in standard ISO 8601 format.
2013-01-01
java.time
The modern solution uses the java.time classes that supplanted the terrible legacy date-time classes bundled with the earliest versions of Java.
Epoch reference date: 1899-12-30
According to this documentation, that value from Microsoft Excel is the number of days since the epoch reference of 1900-01-01 in UTC. Internally, the actual reference date is December 30, 1899 as documented on this Wikipedia page.
Beware, some versions (old versions for macOS?) of Excel use a different epoch in 1904.
Establish the epoch reference somewhere in your code.
final static public LocalDate EXCEL_EPOCH_REFERENCE =
LocalDate.of( 1899 , Month.DECEMBER , 30 )
; // Beware: Some versions of Excel use a 1904 epoch reference.
Do the math
Parse your input string as a BigDecimal for accuracy (versus floating-point types that trade away accuracy for faster execution).
BigDecimal countFromEpoch = new BigDecimal( "41275.00" );
Add the number of whole days to the epoch reference date.
long days = countFromEpoch.longValue(); // Extract the number of whole days, dropping the fraction.
LocalDate localDate = EXCEL_EPOCH_REFERENCE.plusDays( days );
localDate.toString(): 2013-01-01
About java.time
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.
Where to obtain the java.time classes?
-
Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
- Java 9 brought some minor features and fixes.
-
Java SE 6 and Java SE 7
- Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
-
Android
- Later versions of Android (26+) bundle implementations of the java.time classes.
- For earlier Android (<26), a process known as API desugaring brings a subset of the java.time functionality not originally built into Android.
- If the desugaring does not offer what you need, the ThreeTenABP project adapts ThreeTen-Backport (mentioned above) to Android. See How to use ThreeTenABP….
As many users already pointed out, Excel stores dates and times as a number representing the number of days since January 0, 1900, plus a fractional portion of a 24 hour day: ddddd.tttttt.
This is called serial date or serial date-time.
But the answers here represent only a part of the story - except for pointing out ready to use methods from existing libraries.
The linked documentation from Microsoft doesn't make it any more clear.
MS DATEVALUE function states:
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900
Well, I'm going to check this statement:
LocalDate testDate = LocalDate.of(1900, Month.JANUARY, 1).plusDays(39447);
System.out.println(testDate);// prints 2008-01-02
39,447 days after January 1, 1900 it's indeed... January 2, 2008!
Why is that?
The fact that dates in Excel are represented by the number of days starting from an epoch (December 30, 1899 or January 1, 1900 or 1904...) is only a part of the story.
I found an ultimate answer here: Dates And Times In Excel (some god or Whoever may bless these guys).
The developers who implemented the date routines in Excel introduced deliberately a bug for compatibility with the same known issue from Lotus 1-2-3.
They treated the year 1900 as a leap year but it's not,
so any date exceeding January 28, 1900 it's a day more than the actual date.
That's why Excel thinks that January 1, 2008 is represented by the number 39448: because it is 39,448 units after January 0, 1900 (yes, Excel thinks zero) - ie 39,447 days plus January 29, 1900.
Excel can also treat the date portion of a serial date as the number of days since January 0, 1904; this mode is called 1904-mode or 1904-system and it's used for compatibility with the Macintosh systems.
Since Excel dates don't carry any time-zone information - it's only a number - it's better to use Java classes like LocalDate/LocalDateTime to represent such values without a time-zone information.
Well, in pratice - for nowadays dates - one may figure Excel epoch starting from December 30, 1900 but it's not.
Excel demo - date format is dd/mm/yyyy
Dates are inserted as the number on the left
A class suitable for the required conversion:
public class SerialDate {
//days from 1899-12-31 to Instant.EPOCH (1970-01-01T00:00:00Z)
public static final long EPOCH = -25568L;
private long serialDays;
private double serialTime;
private long epochDays;
private long daySeconds;
/**
* @param date number of Excel-days since <i>January 0, 1899</i>
*/
public SerialDate(long date) {
serialDays = date;
if (date > 59)//Lotus123 bug
--date;
epochDays = EPOCH + date;
}
/**
* @param date number of days since <i>January 0, 1899</i> with a time fraction
*/
public SerialDate(double date) {
this((long)date);
serialTime = date - serialDays;
daySeconds = Math.round(serialTime * 24 * 60 * 60);
}
/**
* @return days since 1970-01-01
*/
public long toEpochDays() {
return epochDays;
}
/**
* @return seconds of the day for this SerialDate
*/
public long toDaySeconds() {
return daySeconds;
}
/**
* @return a value suitable for an Excel date
*/
public double getSerialDate() {
return serialTime + serialDays;
}
}
Usage example:
LocalDate dt = LocalDate.ofEpochDay(new SerialDate(41275).toEpochDays());
System.out.println(dt);//prints 2013-01-01
SerialDate sd = new SerialDate(33257.415972222225);
LocalDateTime dt = LocalDateTime.of(
LocalDate.ofEpochDay(sd.toEpochDays()),
LocalTime.ofSecondOfDay(sd.toDaySeconds()));
System.out.println(dt);//prints 1991-01-19T09:59