Ask Your Question

Dates as x-values in chart -

asked 2016-10-16 11:45:12 +0200

rahul36 gravatar image


I plotted a graph which has dates as the x-values. Made a best fit line as well. Now using the function I need to forecast y values for future x-values. But the problem here is all the x-values are dates. So how does Libre Office treat dates when it comes to chart?

Can someone please help me in this regard? This is the chart:

edit retag flag offensive close merge delete


@rahul36: Images are only of value if they show something occurring on your system and not expected to also show on another system. In rare cases an image may help to disambiguate the wording.
In all the other cases, please attach "the real thing": a LibO-document demonstrating the issue.

Lupp gravatar imageLupp ( 2016-10-16 14:02:26 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-10-16 14:17:47 +0200

Lupp gravatar image

updated 2016-10-16 14:41:04 +0200

Dates are by default treated as numbers. Using dates for x-values in a "X-Y (Scatter)" diagram requires the numerical representation.

Calendaric dates are represented by the integer number of days since 1899-12-30 which was defined to be day number zero (an error originally). (There were a few outdated alternatives.)

To apply an extrapolation formula you won't need to do anything about this. The formula will accept the numeric value of any cell independent on the format used for display in this cell.

Your example of application is bringing up two issues, however:
a) The reliance of the y-values on the x-values is clearly not linear (or of another well known functional type).
b) The coefficients taken from the chart's trend-line equation cannot be used as variable paremeters. They must be typed in.

Te second issue can be solved with the help of LINEST (or LOGEST under different conditions). There is also the TREND function.

(By the way: Don't use the USA date format in international communication. To avoid dangerous misinterpretations there is the YYYY-MM-DD format in compliance with ISO 8601.)

edit flag offensive delete link more


Ohh, yes I solved the issue. Libre Office was using inherently generated number for each dates (x values). And only when we substitute these numbers in the f(x), do we get the y value. We can find out this number using the "DATEVALUE("") function. Using this I found out the number corresponding to each date and solved the function to find out the value.

And, sure I will keep in mind to use the YYYY-MM-DD format. I did not know it was ISO 8601 compliant. :) Thank You

rahul36 gravatar imagerahul36 ( 2016-10-16 16:02:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-10-16 11:45:12 +0200

Seen: 2,719 times

Last updated: Oct 16 '16