0

While using fill for dates in Excel, there are 4 options.

Fill Days. Fill Weekdays. Fill Months. Fill Years.

I am trying to implement the basic functionalities of a Spreadsheet application. I have implemented the Fill Series for normal numeric values by employing least squares method by finding a line of best fit (https://superuser.com/questions/1334032/what-exactly-is-excels-auto-fill-algorithm).

If we generally fill dates, it's simple. Because each date value can be translated to some EPOCH value and based on those long integer values, the best fit line can be found and the values can be filled.

Example

1/1/1970 - 1

4/1/1970 - 4

So the values can be found as 7, 10, 13... and the corresponding date values can be generated.

How to employ the same method for Weekdays, Months and Years? Excel's fill for these 3 sets seems to be complex and very hard to comprehend and find a pattern.

Update:

enter image description here

The cells in grey are user entered initial values. I have filled the dates in each column for Weekdays. I am unable to arrive at any kind of pattern in filling dates for weekdays.

Sankaranarayanan G
  • 61
  • 1
  • 9
  • Can you show us a specific example of what you don't understand? – ImaginaryHuman072889 Sep 12 '18 at 10:40
  • Hey @ImaginaryHuman072889, I have updated the question. Kindly check it. – Sankaranarayanan G Sep 12 '18 at 13:03
  • Format the cells as numbers so the date serial numbers are explicit, and see if the patterns become any clearer then. Note that Excel starts off at 1900-01-01, not 1970-01-01, so that may not be the wisest choice for your epoch. – Jeroen Mostert Sep 12 '18 at 13:09
  • Yeah, I have done that. When I convert these date values to a number format, I just get the total number of days elapsed from the start date. For e.g, 1/1/1900 is 1, 2/1/1900, is 2 and so on. I could not catch any patterns from these values because I think the filling algorithm is independent of this number value. – Sankaranarayanan G Sep 12 '18 at 13:26
  • The only real aberration is the Thursday-Sunday pattern, where I don't know how Excel gets to Friday (Monday would make more sense). The other cases are regular, if you inspect the `WEEKDAY()` values of the cells -- if the days are consecutive, you get a linear pattern but skipping Saturday and Sunday; if they're not, the weekday pattern repeats across weeks. – Jeroen Mostert Sep 12 '18 at 13:58

0 Answers0