CODEWITHSUNDEEP
javaexcel
aizaz
aizaz

Reputation: 3062

Converting number representing a date in Excel to a Java Date object

I have a date column in Excel, but when I'm reading it in my Java application I'm getting the value as number.

Example

Excel Date

1/1/2013

I'm getting it as

41275.00

How to convert the number to a date in my Java application?

Upvotes: 18

Views: 48822

Answers (6)

Werner Diwischek
Werner Diwischek

Reputation: 191

I used the following code according to the SerialDate class example for LocalDateTime:

// somewhere as double static 
LocalDateTime EXCEL_START = LocalDateTime.of(1899, 12, 30, 0, 0);
Double doubleValue = 44705.416666666664; // input

long days = doubleValue.longValue();
long seconds = Math.round((doubleValue - days) * 24 * 60 * 60);
LocalDateTime converted = EXCEL_START
            .plusDays(days)
            .plusSeconds(seconds); //2022-05-24T10:00

Upvotes: 0

fantaghirocco
fantaghirocco

Reputation: 4878

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

7
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

Upvotes: 4

Basil Bourque
Basil Bourque

Reputation: 339502

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?

Upvotes: 20

宏杰李
宏杰李

Reputation: 12168

Excel’s serialized dates are the number of days since 1/1/1900. In order to figure out the date again, we have to add the serial number worth of days.

for Java 8 without any dependency

```

  /*

    1900-1-0            0
    1900-1-1            1
    1900-1-2            2
    1900-1-3            3


     */


    int days = 43323;
    LocalDate start = LocalDate.of(1900, 1, 1);
    LocalDate today = LocalDate.of(2018, 8, 11);


    // days to date
    LocalDate date = start.plusDays(days).minusDays(2);

    System.out.println(date);

    // date to days
    long days1 = ChronoUnit.DAYS.between(start, today) + 2;
    System.out.println(days1);

```

Upvotes: 0

cemal
cemal

Reputation: 539

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

Upvotes: 27

Jeff Storey
Jeff Storey

Reputation: 57202

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.

Upvotes: 16

Related Questions