# jmeter-user mailing list archives

##### Site index · List index
Message view
Top
From sebb <seb...@gmail.com>
Subject Re: timestamp field in xml result files
Date Tue, 18 Nov 2008 12:47:51 GMT
```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 <niyas.mohd@gmail.com> 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
>  niyas.mohd@gmail.com
>  On Tue, Nov 18, 2008 at 5:24 AM, Steve Kapinos
>
> <Steve.Kapinos@tandberg.com>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:Steve.Kapinos@tandberg.com]
>  > >  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: jmeter-user-unsubscribe@jakarta.apache.org
>  > >  For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>  > >
>  > >
>  > >  ---------------------------------------------------------------------
>  > >  To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>  > >  For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>  > >
>  > >
>  >
>  > ---------------------------------------------------------------------
>  > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>  > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>  >
>  >
>  >
>  > ---------------------------------------------------------------------
>  > To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
>  > For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>  >
>  >
>
>
>
> --
>  Regards,
>  Mohamed Niyas M
>  Mobile: 98804 59080 niyas.mohd@gmail.com
>  to_niyas@yahoo.co.in
>

---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org