Number is a time value.Ĭalculates the difference, in days, between two date values. A negative date/time value can be entered. Returns the day, as an integer, of the given date value. Text is a valid date expression and must be entered with quotation marks. Returns the internal date number for text in quotes.

Year is an integer between 15 or 0 and 99. To keep dates from being interpreted as parts of formulas, and thus returning erroneous results, always place them in quotation marks, for example, '12/08/52'.Ĭonverts a date written as year, month, day to an internal serial number and displays it in the cellâ€™s formatting. When entering dates, slashes or dashes used as date separators may be interpreted as arithmetic operators. go to Tools > Options > Calc > Calculate. To set the default date format used by Calc. To see the date or time displayed in a standard format, change the number format (date or time) accordingly. This is just a matter of formatting the actual value is always stored and manipulated as a number. When you assign the number format â€œNumberâ€ to a date or time value, it is displayed as a number. handles and computes a date/time value as a number.

And if you format that cell as a date, DD/MM/YYYY HH:MM:SS, then you'll read a pretty ' 01:00:00' there.Use these functions for inserting, editing and manipulating dates and times. And if you sum that number with 25569, you have an OpenOffice day for that timestamp.Īlright, let's put all the pieces together: let's say cell A2 contains a UNIX timestamp 1341104400, then this formula =A2/86400+25569 Now, if you divide a UNIX timestamp by 86400 (the number of seconds in a normal day), that will give you the number of days between the epoch and that timestamp (and some decimal, that you can use to calculate the time of day). As it seems, OpenOffice's 'day 0' is December 12th, 1899 that implies that January 1st, 1970 is day 25569 for OpenOffice. The problem is: you have a time series of data in a spreadsheet, and the time is expressed as UNIX timestamps (seconds since the epoch, 00:00:00 UTC, and not taking into account leap seconds): how can you convert those timestamps in a readable date like, e.g., ' 01:00:00'? … I found a very useful suggestion in the OpenOffice community forum. This is not something I discovered myself, rather it's something that I'll need every now and then when doing extemporary reports of UNIX-timestamped data.

Openoffice calculate hours from time