Convert Date for Use in an X-Y Plot Trend Formula

I have an X-Y chart that plots appointment percent on the y-axis vs date on the x-axis. The chart works fine. I displayed a trend line for the data and the line predicts when the appointments will hit 100%. There is a formula for the line that is also displayed in the chart. But when I try to use a date for "x" in the formula I get an error saying that multiplication expects a number. How can I convert the date to a form that is usable in the trend formula?


iMac 27″, macOS 10.13

Posted on Mar 11, 2021 3:08 AM

Reply
Question marked as Top-ranking reply

Posted on Mar 12, 2021 7:50 AM

A different thought:


I used the LINEST formula on your data to get the slope and intercept and it appears that it treats the Y axis as the date 1/1/2001. Every other date is "days since, or before, 1/1/2001". The trend line equation on the chart is somewhat different. In it, the other dates are "seconds since 1/1/2001 12:00AM".


The problem you were having with the actual dates and the problem now with your numeric "dates" is due to rounding of the slope and intercept in the formula on the chart. The numbers being used for X are very large compared to the minuscule percentage values for Y. You will have to calculate the slope and intercept yourself in your table using LINEST to get the precision you need.


And for some additional info that I already typed up before the stuff I added above:


One date structure of Excel uses 12/31/1899 as day 0. I often try to stay consistent with Excel but this time it is a problem. Numbers counts 1900 as a leap year when it is not. So if you use 12/31/1899, your index numbers will not match Excel's. I wonder if Numbers gets other non-leap years wrong. 1900 is a century year not divisible by 400 so is not a leap year. I have not tested any others. To be consistent with Excel for dates after 2/28/1900, you would need to use 12/30/1899 as the starting date.


I had no issues using "1/1/0001" in DATEDIF and I am getting different results than you.

=DATEDIF("1/1/0001",C2,"D") where C2 has 3/15/2021 was not an error. It gave me the value 737865

737865+366 = 738231 (not the 738229 that you got)

4 replies
Question marked as Top-ranking reply

Mar 12, 2021 7:50 AM in response to Tom Cloutier

A different thought:


I used the LINEST formula on your data to get the slope and intercept and it appears that it treats the Y axis as the date 1/1/2001. Every other date is "days since, or before, 1/1/2001". The trend line equation on the chart is somewhat different. In it, the other dates are "seconds since 1/1/2001 12:00AM".


The problem you were having with the actual dates and the problem now with your numeric "dates" is due to rounding of the slope and intercept in the formula on the chart. The numbers being used for X are very large compared to the minuscule percentage values for Y. You will have to calculate the slope and intercept yourself in your table using LINEST to get the precision you need.


And for some additional info that I already typed up before the stuff I added above:


One date structure of Excel uses 12/31/1899 as day 0. I often try to stay consistent with Excel but this time it is a problem. Numbers counts 1900 as a leap year when it is not. So if you use 12/31/1899, your index numbers will not match Excel's. I wonder if Numbers gets other non-leap years wrong. 1900 is a century year not divisible by 400 so is not a leap year. I have not tested any others. To be consistent with Excel for dates after 2/28/1900, you would need to use 12/30/1899 as the starting date.


I had no issues using "1/1/0001" in DATEDIF and I am getting different results than you.

=DATEDIF("1/1/0001",C2,"D") where C2 has 3/15/2021 was not an error. It gave me the value 737865

737865+366 = 738231 (not the 738229 that you got)

Mar 12, 2021 4:01 AM in response to Badunit

That gets me a number that is usable. I had trouble getting the function to give me a number for a date that equates to the year 0. I need that for the formula to work. DATEDIF didn't like "1/1/00"; that equated to the year 2000. It didn't like "1/1/0000"; it gave an error. It also gave an error when I used "1/1/0001". It did give me a number when I used "1/1/001" which I assume equated to January 1 of the year 1. So I ended up using DATEDIF("1/1/001",D5,"D")+366 (where D5 contains the date "3/15/2021"), assuming the year 0 was a leap year where I got the number 738229 using 3/15/2021, the contents of D5, as the second date. As a check, 365.25*2021 gives 738170.25 (which would equate to Jan. 1, 2021) so it seems right. But using 738229 for x got a formula answer of -2.83% instead of something close to 100%. So something else is wrong. But I at least learned how to convert a date to a number and I thank you for that.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Convert Date for Use in an X-Y Plot Trend Formula

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.