How can I parse this numbers to timestamp?

Question

I have a excel file I read in java. There is a column contains dates. if I format cells a can see real dates. I have no problem in excel but i read this cells as string in java times looks like this;

as String -> 43706,4856597222 as Date in excel -> 29/08/2019 11:39:21

what is this "43706,4856597222" format ? is it some kind of timestamp?

Answer 1

Excel represents datetime as a decimal number in which the integral represents the number of days since the epoch of 0 January 1900 (or properly 31st December 1899) and the fractional portion represents time as a fraction of a day.

Helpfully, Excel erroneously assumes that the year 1900 is a leap year.

There are 86400 seconds in a day.

With this knowledge it is fairly straightforward to write a function that parses an Excel datetime representation to a LocalDateTime object within Java.

import java.time.LocalDateTime;

public class ExcelParser
{
    public static void main(String[] args)
    {
        String s = "43706.4856597222";
        LocalDateTime testDate = parseExcelDate(s);
        System.out.println(testDate);
    }
    
    public static LocalDateTime parseExcelDate(String s)
    {
        long days = Long.parseLong(s.split("\\.")[0]); // Get days from epoch as long
        double seconds = (Double.parseDouble(s) - days) * 86400; // Get time fraction and convert to seconds
        if (days >= 59)
        {
            days--; // Shave extra day if days push date past erroneous leap day in 1900
        }
        // Construct and return LocalDateTime object
        return LocalDateTime.of(1899, 12, 31, 0, 0, 0).plusDays(days).plusSeconds((long) Math.ceil(seconds));
    }
}

Output

2019-08-29T11:39:21

Obviously the above is just an example solution and does not feature Exception handling, but it should give you an idea of how you might go about solving this problem.