Microsoft apparently stores dates as an integer number of days since Jan. 1, 1900. I need to find some Java classes which will handle Microsoft format dates and figure what date is represented and create a string containing a human-readable formated date. Classes that went the other way, converting a human-readable date in the proper format into an integer number of days since 01/01/1900, would also be needed. Does anyone know a set of classes which are available and downloadable? Your help would be greatly appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using Microsoft format dates in Java 1
- Thread starter mcklsn
- Start date
-
1
- #2
thekobbler
Programmer
This code does it.. simple.
import java.util.*;
public class TestCalendar {
public static Calendar getDateFromMS(int numbOfDaysSince1900_fromMicrosoft) {
Calendar c = Calendar.getInstance();
c.set(1900, 0, 0);
c.add(Calendar.DAY_OF_MONTH, numbOfDaysSince1900_fromMicrosoft);
return c;
}
public static void main(String[] args) {
Calendar c = TestCalendar.getDateFromMS(37696);
System.out.println(c.getTime());
}
}
import java.util.*;
public class TestCalendar {
public static Calendar getDateFromMS(int numbOfDaysSince1900_fromMicrosoft) {
Calendar c = Calendar.getInstance();
c.set(1900, 0, 0);
c.add(Calendar.DAY_OF_MONTH, numbOfDaysSince1900_fromMicrosoft);
return c;
}
public static void main(String[] args) {
Calendar c = TestCalendar.getDateFromMS(37696);
System.out.println(c.getTime());
}
}
thekobbler
Programmer
I've had another look at this one, out of curosity.. try this..
import java.util.*;
public final class MSCalendarConverter {
private static Calendar c1900 = null;
private static Date d1900 = null;
private static long milliSecInDay = 1000 * 24 * 60 * 60;
/**
* Needs to be called once to setup the static values.
*/
private static void init() {
c1900 = Calendar.getInstance();
c1900.set(1900, 0, 0);
c1900.set(Calendar.HOUR_OF_DAY,0);
c1900.set(Calendar.MINUTE,0);
c1900.set(Calendar.SECOND,0);
c1900.set(Calendar.MILLISECOND,0);
d1900 = c1900.getTime();
}
/**
* Given the number of days since the start of 1900, this method returns
* the equivalent Calenday object.
*/
public static Date getDateFromMS(int numDaysSince1900) {
if(c1900 == null) init();
Date d = new Date(d1900.getTime() + numDaysSince1900*milliSecInDay);
return new Date(d1900.getTime() + numDaysSince1900*milliSecInDay);
}
/**
* Given a Date object, this method returns the number of <B>whole</B> days passed
* since the start of 1900.
*/
public static int getDaysSince1900(Date d) {
if(c1900 == null) init();
// get time difference (in millisecs.)
long delta = d.getTime() - d1900.getTime(); // millisec
long days = delta/milliSecInDay;
return (int)days+1; // + one as MS day 1 is 1/1/1900.
}
public static void main(String[] args) {
int nDays = 1;
Date d = getDateFromMS(nDays);
System.out.println("1900 + "+nDays+"day(s) = "+d); // 1 day after 1/1/1900 is the 2/1/1900
Date now = new Date(System.currentTimeMillis());
System.out.println("days since 1900 = "+getDaysSince1900(now));
}
}
import java.util.*;
public final class MSCalendarConverter {
private static Calendar c1900 = null;
private static Date d1900 = null;
private static long milliSecInDay = 1000 * 24 * 60 * 60;
/**
* Needs to be called once to setup the static values.
*/
private static void init() {
c1900 = Calendar.getInstance();
c1900.set(1900, 0, 0);
c1900.set(Calendar.HOUR_OF_DAY,0);
c1900.set(Calendar.MINUTE,0);
c1900.set(Calendar.SECOND,0);
c1900.set(Calendar.MILLISECOND,0);
d1900 = c1900.getTime();
}
/**
* Given the number of days since the start of 1900, this method returns
* the equivalent Calenday object.
*/
public static Date getDateFromMS(int numDaysSince1900) {
if(c1900 == null) init();
Date d = new Date(d1900.getTime() + numDaysSince1900*milliSecInDay);
return new Date(d1900.getTime() + numDaysSince1900*milliSecInDay);
}
/**
* Given a Date object, this method returns the number of <B>whole</B> days passed
* since the start of 1900.
*/
public static int getDaysSince1900(Date d) {
if(c1900 == null) init();
// get time difference (in millisecs.)
long delta = d.getTime() - d1900.getTime(); // millisec
long days = delta/milliSecInDay;
return (int)days+1; // + one as MS day 1 is 1/1/1900.
}
public static void main(String[] args) {
int nDays = 1;
Date d = getDateFromMS(nDays);
System.out.println("1900 + "+nDays+"day(s) = "+d); // 1 day after 1/1/1900 is the 2/1/1900
Date now = new Date(System.currentTimeMillis());
System.out.println("days since 1900 = "+getDaysSince1900(now));
}
}
- Thread starter
- #4
Thanks, Kobbler, but I have to confess that I'm fairly new to Java and I don't understand all of your code. I've been perusing the JavaDocs trying to understand the Date and Calender utilities and my head is spinning. What I need, I think, is more simple than what you sent me. What I need is as follows: I need a method which will take the number of days since 01/01/1900 and give me a string in the format yyyy/mm/dd, and another method which will take a string formated the same way, yyyy/mm/dd, and give me the number of days since 01/01/1900. In my application I don't need and can't use any information below the day level (i.e. hours, minutes, seconds, etc.). Would you care to do some more thinking for me? Thanks very much, mcklsn.
- Thread starter
- #5
thekobbler
Programmer
It is always best to break a problem down to its smaller components, i.e. increase it's granularity. So points to note..
- Java has a Date class that is useful for doing data manipulation.
- The data class uses a millisecond count from 1/1/1970 00:00:00:00. So it is not directly compatable with MS's date numbering system.
- There is a String formatter for the Date class, so we should reuse, it (see java.text package for other useful things!).
- We should not build a single method that is too complex.
The last point is where good design practise comes in. In your example we could create a method that does exactly what it says on the tin, i.e. given an integer (days since 1900) it restuns a String og the form yyyy/mm/dd. Very nice, but what if in the future we want to make this return a String in the normal (UK) format, i.e. dd/mm/yyyy. We would have to create a second method, and copy a lot of the funhctionality of the first. It would therefore be better to have several methods, which could be reused.
So, in the code I gave above, the method MSCalendar.getDateFromMS(int) returns a java.util.Date class representing the MS date. We can subsequently use the java.text.SimpleFormatDate object to stringify the Date.
So in the calling method (i.e. the main in our example above) simply use...
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd"
; // NOTE MM pattern must be in Capitals!
Date d2 = getDateFromMS(37698);
String dateStr = df.format(d2);
System.out.println("1900 + "+nDays+"day(s) = "+dateStr);
where dateStr now is "2003/03/19".
The reverse is a bit more complex but nearly as simple. Knowing the format of the String, say "yyyy/MM/dd" we can use a SimpleDateFormat object to parse the String into a Date object, using simpleDateFormatter.parse(String dateString).
The following is a repeat of the above class, but with extra calls made in the main method to demonstrate the formatting, and parsing of Stringified dates.
import java.util.*;
import java.text.*;
public final class MSCalendarConverter {
private static Date d1900 = null; // the date 1/1/1900 00:00:00:00
private static long milliSecInDay = 1000 * 24 * 60 * 60;
/**
* Needs to be called once to setup the 1900 00:00:00:00 date.
*/
private static void init() {
Calendar c1900 = Calendar.getInstance();
c1900.set(1900,0,0);
c1900.set(Calendar.HOUR_OF_DAY,12);
c1900.set(Calendar.MINUTE,0);
c1900.set(Calendar.SECOND,0);
c1900.set(Calendar.MILLISECOND,0);
d1900 = c1900.getTime();
}
/**
* Given the number of days since the start of 1900, this method returns
* the equivalent Calenday object.
*/
public static Date getDateFromMS(int numDaysSince1900) {
if(d1900 == null) init();
Date d = new Date(d1900.getTime() + (numDaysSince1900-1)*milliSecInDay);
return d;
}
/**
* Given a Date object, this method returns the number of <B>whole</B> days passed
* since the start of 1900.
*/
public static int getDaysSince1900(Date d) {
if(d1900 == null) init();
// get time difference (in millisecs.)
long delta = d.getTime() - d1900.getTime(); // millisec
long days = delta/milliSecInDay;
return (int)days+2;
}
public static void main(String[] args) {
int nDays = 37698; // todays (18th March 2003) date number, from Excel
Date d = getDateFromMS(nDays);
System.out.println("1900 + "+nDays+" day(s) = "+d); // 1 day after 1/1/1900 is the 2/1/1900
// create a Date formatter...
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd"
// apply Date formatter...
String dateStr = df.format(d);
// print result....
System.out.println("1900 + "+nDays+" day(s) = "+dateStr);
try {
Date d3 = df.parse(dateStr);
System.out.println("parsed version = " + d3 + " or in 'yyyy/mm/dd' format = "+df.format(d3));
System.out.println("days since 1900 = "+getDaysSince1900(d3));
}
catch (java.text.ParseException pe) {
System.out.println(pe.getMessage());
}
}
}
I've noticed a strange "feature". If you use Excel to set Date fields and enter 1 it returns 01/01/1900, and 37698 returns 18/03/2003. BUT if you use the above code and enter 1 it returns 1899/12/31 , and 37698 returns 18/03/2003 as expected..... strange in deed!?
- Java has a Date class that is useful for doing data manipulation.
- The data class uses a millisecond count from 1/1/1970 00:00:00:00. So it is not directly compatable with MS's date numbering system.
- There is a String formatter for the Date class, so we should reuse, it (see java.text package for other useful things!).
- We should not build a single method that is too complex.
The last point is where good design practise comes in. In your example we could create a method that does exactly what it says on the tin, i.e. given an integer (days since 1900) it restuns a String og the form yyyy/mm/dd. Very nice, but what if in the future we want to make this return a String in the normal (UK) format, i.e. dd/mm/yyyy. We would have to create a second method, and copy a lot of the funhctionality of the first. It would therefore be better to have several methods, which could be reused.
So, in the code I gave above, the method MSCalendar.getDateFromMS(int) returns a java.util.Date class representing the MS date. We can subsequently use the java.text.SimpleFormatDate object to stringify the Date.
So in the calling method (i.e. the main in our example above) simply use...
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd"
; // NOTE MM pattern must be in Capitals!Date d2 = getDateFromMS(37698);
String dateStr = df.format(d2);
System.out.println("1900 + "+nDays+"day(s) = "+dateStr);
where dateStr now is "2003/03/19".
The reverse is a bit more complex but nearly as simple. Knowing the format of the String, say "yyyy/MM/dd" we can use a SimpleDateFormat object to parse the String into a Date object, using simpleDateFormatter.parse(String dateString).
The following is a repeat of the above class, but with extra calls made in the main method to demonstrate the formatting, and parsing of Stringified dates.
import java.util.*;
import java.text.*;
public final class MSCalendarConverter {
private static Date d1900 = null; // the date 1/1/1900 00:00:00:00
private static long milliSecInDay = 1000 * 24 * 60 * 60;
/**
* Needs to be called once to setup the 1900 00:00:00:00 date.
*/
private static void init() {
Calendar c1900 = Calendar.getInstance();
c1900.set(1900,0,0);
c1900.set(Calendar.HOUR_OF_DAY,12);
c1900.set(Calendar.MINUTE,0);
c1900.set(Calendar.SECOND,0);
c1900.set(Calendar.MILLISECOND,0);
d1900 = c1900.getTime();
}
/**
* Given the number of days since the start of 1900, this method returns
* the equivalent Calenday object.
*/
public static Date getDateFromMS(int numDaysSince1900) {
if(d1900 == null) init();
Date d = new Date(d1900.getTime() + (numDaysSince1900-1)*milliSecInDay);
return d;
}
/**
* Given a Date object, this method returns the number of <B>whole</B> days passed
* since the start of 1900.
*/
public static int getDaysSince1900(Date d) {
if(d1900 == null) init();
// get time difference (in millisecs.)
long delta = d.getTime() - d1900.getTime(); // millisec
long days = delta/milliSecInDay;
return (int)days+2;
}
public static void main(String[] args) {
int nDays = 37698; // todays (18th March 2003) date number, from Excel
Date d = getDateFromMS(nDays);
System.out.println("1900 + "+nDays+" day(s) = "+d); // 1 day after 1/1/1900 is the 2/1/1900
// create a Date formatter...
SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd"
;// apply Date formatter...
String dateStr = df.format(d);
// print result....
System.out.println("1900 + "+nDays+" day(s) = "+dateStr);
try {
Date d3 = df.parse(dateStr);
System.out.println("parsed version = " + d3 + " or in 'yyyy/mm/dd' format = "+df.format(d3));
System.out.println("days since 1900 = "+getDaysSince1900(d3));
}
catch (java.text.ParseException pe) {
System.out.println(pe.getMessage());
}
}
}
I've noticed a strange "feature". If you use Excel to set Date fields and enter 1 it returns 01/01/1900, and 37698 returns 18/03/2003. BUT if you use the above code and enter 1 it returns 1899/12/31 , and 37698 returns 18/03/2003 as expected..... strange in deed!?
- Status