0

In the below image they have calculated the unformatted value of the DateTime for calculating the slope and intercept value from the given data. How did I achieve this in a mathematical way? enter image description here

polynomial equation :

enter image description here

yuva
  • 436
  • 4
  • 9
  • 2
    Dates are just doubles formatted to look like a date. `42814` is the number of days since 1/1/1900. So the formatting/unformatting is not needed as long as the dates are true dates and not text that looks like a date. – Scott Craner Aug 11 '21 at 19:30

2 Answers2

1

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

from https://support.microsoft.com/en-us/office/datevalue-function-df8b07d4-7761-4a93-bc33-b7471bbff252

By "mathematical way" you would need to count the number of day since Jan 1, 1900. Don't forget leap years.

I could also interpret your question as how to calculate the slope and intercept?

Philip
  • 147
  • 7
  • Hi, @philip thanks for your response it is working, and I have calculated the slope and intercept myself, but I couldn't be able to calculate an R squared value can you please help me with that. – yuva Aug 12 '21 at 07:18
  • The calculation is here ... https://ashutoshtripathi.com/2019/01/22/what-is-the-coefficient-of-determination-r-square/ . The second equation with the title "Coefficient of Determination (R Squared)" is the easiest to understand. The notation, if you're not familiar is, y with a horizontal dash on top is "y bar". y with a greater than symbol pointing up is "y hat" and it means "predicted". Finally, yi is the ith data point. With those three numbers, you can calculate Rsq. – Philip Aug 12 '21 at 15:09
  • Thanks for the solution that was so helpful. I have tried to form a slope equation for the polynomial trendline type with the date-time type value, but I couldn't able to form a slope equation. here is the expected output [image](https://i.stack.imgur.com/DLh7k.png) for reference. I didn't get any solution can you please help me to form a polynomial equation. Also, the R square value for exponential trendline type is not the same as the excel R square value for date-time type value when proceeding with the steps you gave above. Can you please help me with this issue? – yuva Aug 18 '21 at 10:09
  • I am happy to, but it seems that this would be better as a different post. Start a new question and post your code. Just let me know when the new question is up. – Philip Aug 18 '21 at 20:33
  • Hi @philip I have created the separate question as you said, can you please help me with that https://stackoverflow.com/questions/68853094/how-to-form-a-slope-equation-for-the-polynomial-trendline-and-calculate-a-r-squa – yuva Aug 19 '21 at 18:44
0

you can also just change the format from date to number will give you the numeric result..

Khaled Sayed
  • 70
  • 6