Convert OLE to Readable format

Respected Readers,

This post describes algorithm for converting OLE time format in to readable time format. I was parsing this new file format that stored date in OLE time format. It looks like a double type. With some inputs from file format guys, I got logic behind this format. The integer part represents number of days passed since 30-DEC-1899 (some lucky date). The fractional part represents fraction of 24-hour day that has elapsed.

Eg:

OLE Time –> Readable Time

0 --> 01/30/1900 0:0:0
2.75 --> 02/01/1900 18:0:0
-1.25 --> 01/29/1900 6:0:0
35065 --> 02/01/1996 0:0:0
40648.05932293980 --> 05/16/2011 1:25:25

So I wrote this Java method that would take OLE time format input and return date in a readable text format.

	public static String getTimeFromOLETime(String oleTime) {
		SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
		String date = null;
		String time = null;

		if (oleTime ==  null || oleTime.length() == 0) {
			return null;
		}

		if (!oleTime.contains(".")) {
			oleTime += ".0";
		}

		int daysElapsed = Integer.parseInt(oleTime.substring(0, oleTime.indexOf(".")));

		double hoursElapsed = 24 * Double.parseDouble("0." + oleTime.substring(oleTime.indexOf(".") + 1, oleTime.length()));

		String minutesElapsedTemp = Double.toString(hoursElapsed);
		double minutesElapsed = 60 * Double.parseDouble("0." + minutesElapsedTemp.substring(minutesElapsedTemp.indexOf(".") + 1));

		String secondsElapsedTemp = Double.toString(minutesElapsed);
		double secondsElapsed = 60 * Double.parseDouble("0." + secondsElapsedTemp.substring(secondsElapsedTemp.indexOf(".") + 1));

		time = Double.toString(hoursElapsed).substring(0, Double.toString(hoursElapsed).indexOf(".")) + ":"
				+ Double.toString(minutesElapsed).substring(0, Double.toString(minutesElapsed).indexOf(".")) + ":"
				+ Double.toString(secondsElapsed).substring(0, Double.toString(secondsElapsed).indexOf("."));

		Calendar c1 = Calendar.getInstance();
		c1.set(1899, 11, 30);
		c1.add(Calendar.DAY_OF_MONTH, daysElapsed);

		date = sdf.format(c1.getTime());

		return date + " " + time;

	}

Hope that saves some time for you.

Thanks,

Badal

Advertisements

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Core Java, Oracle and tagged , , , . Bookmark the permalink.

3 Responses to Convert OLE to Readable format

  1. Anonymous says:

    Badal,
    The values for the second argument of the Calendar.set() method range from 0 (January) to 11 (December). Line 21 of the method above should therefore be: c1.set(1899, 11, 30);
    I noticed that the Recorded Timestamp values in the Drive Test module were all 1 month later than the actual date.
    Regards,
    Dan

  2. Changed c1.set(1899, 12, 30) to c1.set(1899, 11, 30) as month is 0-based in Calendar class http://docs.oracle.com/javase/1.5.0/docs/api/java/util/Calendar.html#set(int, int, int)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s