What format can I use in the time fields for Excel?

[samselimi]

I am exporting the data from tickets.cgi  to be displayed on it's own in Excel. I am editing the data in Excel but the time fields don't convert easily in Excel. What Time/Date Format can I use in Excel to display a real time/Date rather that just numbers like 1234903780?

Thanks in advance.

Sam

[Sparky]

In the sticky thread called, Demo, Documentation, & Support...

http://forum.unitedwebcoders.com/index.php/topic,500.0.html

You'll find a link called, Date Format Explained...

http://forum.unitedwebcoders.com/index.php/topic,456.0.html

After that, you should have enough information to construct an Excel formula to convert it.

[Sparky]

http://forum.unitedwebcoders.com/index.php/topic,456.msg2421.html#msg2421

So it looks like it is the number of seconds since January 1, 1970, and ttx stores the dates for open, close, update in UTC (ie in the above example, "my" time for the item shows 17 hours, but the logged time is 22 hours, and my timezone is UTC - 5 hours.

[webcarvallo]

My friend:   The solution is quite simple.

You need to use this formula in excel to get the date (nor the time):

=(((YOUR_ORIGIN_CELL/60)/60)/24)+DATE(1970;1;1)   or mor simple: =((YOUR_ORIGIN_CELL/3600)/24)+DATE(1970;1;1)

You may need to replace ";" for "," depending your excel configuration.

And that´s all.  Quick & Simple.  You'll get the correct date.

Cheers.

I am exporting the data from tickets.cgi  to be displayed on it's own in Excel. I am editing the data in Excel but the time fields don't convert easily in Excel. What Time/Date Format can I use in Excel to display a real time/Date rather that just numbers like 1234903780?

Thanks in advance.

Sam