The formula: =(((X)/1000+((365*70+17)*86400))/86400)+2
is the same as =(((X)/1000+((365*70+19)*86400))/86400) which is the same as =X/(1000*86400) + 25569 The number 25569 agrees with the Excel formula =DATE(1970,1,1) expressed as a number. What the above is doing is: * Convert X from milliseconds to days (86400 = number of seconds in a day) * Add the Excel offset for Jan 1, 1970. Since JavaTime 0 (and Unix time 0) are both Jan 1, 1970, but Excel uses an earlier date, you need to add whatever value Excel uses for Jan 1, 1970, i.e. 25569. I can confirm that the formula also works in OOo Scalc. For Unix => Excel, the formula is =X/86400 + 25569 because Unix times are in seconds. I don't know why the original poster apparently gets better results with adding 25567, rather than 25569. Does not seem right to me now. On 18/11/2008, Mohamed Niyas <[EMAIL PROTECTED]> wrote: > Hi Steve, > > Sometimes back i also came across to findout the timestamps conversion in > XL... > You can use the below in Excel, which it will get the right datetime > conversions... > But i dont know about the Unix timestamp. > > *Convert Timestamps to Excel Format* : > > Once the data is in Excel, I convert the timestamp column from Jmeter's Unix > timestamp > format (base year 1970) to the Excel format (base year 1900) using this > following formula. > This formula is applied to the entire timestamp column. > > For GMT time > > =(((X)/1000+((365*70+17)*86400))/86400)+2 > > For local time (replace t with your current offset from GMT) > > =(((X)/1000+(t * 3600)+((365*70+17)*86400))/86400)+2 > > I have cross checked this validations... You can try out... > > Regards > Mohamed Niyas M > [EMAIL PROTECTED] > On Tue, Nov 18, 2008 at 5:24 AM, Steve Kapinos > > <[EMAIL PROTECTED]>wrote: > > > > According to > > > http://office.microsoft.com/en-us/excel/HP052006741033.aspx?pid=CH010004931033Excel > uses a base of Jan 1, 1900 and counts DAYS not seconds using a real > > > number. > > > > >> I'm not quite sure why this vs just =UnixTime / 86400 + 25569 > > > > >Where does 25569 come from? > > > > Saw it here - and on many other pages > > http://excel.tips.net/Pages/T002051_Converting_UNIX_DateTime_Stamps.html > > > > But that didn't work.. the more complex one did.. > > > > -Steve > > > > > -----Original Message----- > > > From: Steve Kapinos [mailto:[EMAIL PROTECTED] > > > Sent: Monday, November 17, 2008 9:34 AM > > > To: JMeter Users List > > > Subject: timestamp field in xml result files > > > > > > I'm trying to post process some result files which were saved to xml and > > > having some trouble with the ts field. I assume this to be a unix > > > timestamp, but can't convert it successfully to a format excel likes and > > > can't find any additional details in jmeter.properties as per the > > > documentation. > > > > > > What format is this ts field stored in? > > > > > > Thx > > > > > > > > > --------------------------------------------------------------------- > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > > --------------------------------------------------------------------- > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > -- > Regards, > Mohamed Niyas M > Mobile: 98804 59080 [EMAIL PROTECTED] > [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
