1

I have sort of double format number as string uploaded file that each of them equal to specific time like below: (in fact I must convert the left hand side to the right hand side)

40981.0500559 = 13.03.2012 01:12:04.000

40981.0500676 = 13.03.2012 01:12:05.000

40981.0500792 = 13.03.2012 01:12:06.000

40981.0500910 = 13.03.2012 01:12:07.000

40981.0501025 = 13.03.2012 01:12:08.000

I try with excel and find out the first five digit shows the date and count-up from 1900 which means 00000. and for every day it is become +1 increased. for the rest 7 digit after . it shows the time from 00:00:00 when it is .0000000 and for every 0.0000116 it is increase on second in the time till reach to 0.9999999 that is equal 00:00:00 again. but I'm really confused about the way out to convert in java. I tried this:

//Time[j] = 40981.0500559 as string format**

double T = Double.parseDouble(Time[j])*100000000- 2766489481590L; 
DateFormat formatter = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss.FFF");
System.out.println(formatter.format(T));

but it will get wrong date when I change the input value. For example 40993.2891410 should become 25/03/2012 06:56:21.782 but I know above command is not calibrated for all number.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Morteza
  • 13
  • 7
  • This is Java right? Please tag it as such. – imulsion Jul 12 '13 at 18:14
  • possible duplicate of [OLEDate java implementation](http://stackoverflow.com/questions/2599102/oledate-java-implementation) – Matt Johnson-Pint Jul 12 '13 at 18:24
  • 1
    You have an "OLE Automation Date", aka "OLEDate" or "OADate". You can read about them in the "Remarks" section of [this MSDN article](http://msdn.microsoft.com/en-us/library/system.datetime.tooadate.aspx). See [this question](http://stackoverflow.com/q/2599102/634824) or [this question](http://stackoverflow.com/q/9733254/634824) for using them in Java. – Matt Johnson-Pint Jul 12 '13 at 18:27
  • @Matt Interesting.. what's the idea behind this format? – Michel Feldheim Jul 12 '13 at 20:41
  • @MichelFeldheim - It's an old format, rarely required these days, and certainly not desired. There's some background [here](http://blogs.msdn.com/b/ericlippert/archive/2003/09/16/eric-s-complete-guide-to-vt-date.aspx). – Matt Johnson-Pint Jul 12 '13 at 20:51

1 Answers1

0

Try this

 long T = (long)(Double.parseDouble("40981.0500559")*100000000- 2766489481590L);           
 Date date=new Date(T);                                                                    
 DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss.F");                   
 System.out.println(formatter.format(date));
Ruchira Gayan Ranaweera
  • 34,993
  • 17
  • 75
  • 115
  • There are some issues still there, I will look in to them – Ruchira Gayan Ranaweera Jul 12 '13 at 18:41
  • Why the the format string using `FFF`? `F` is documented as the "day of the week in the month"... – Dilum Ranatunga Jul 12 '13 at 18:41
  • Hi for example "40981.0500559" it should be 13.03.12 01:12:04.830 but with my and your format date is correct bu time become 08:42:04.002 I try another input data and with long duration even date will become wrong. I think subtraction that long number to bring it into the java standard millisecond time make it wrong to find the correct date and time. – Morteza Jul 12 '13 at 18:49
  • You are right HH:mm:ss.F is not right it should be HH:mm:ss.SSS but in does not make any change in the problem occured to find the right answer of date and time... – Morteza Jul 13 '13 at 18:27