How can I autofill weekday dates in sequence in Numbers; like 01/01/20, 01/02/20, 01/03/20, 01/06/20, 01/07/20, 01/08/20, 01/09/20, 01/10/20; including all workday dates but excluding the weekend dates?
MacBook Air 13", macOS 10.14
SunCityLady wrote:
couldn't figure out how to autofill only the weekdays.
You can do this without a complex formula with conditionals. It can be as simple as this:
Fill in the first 5 workdays (in this example 30Dec through 3Jan). You can do that by filling in 30Dec, then dragging the yellow dot down 4 rows.
Then in the next cell below that put this formula, referring to the cell with the first Monday (in this example A2):
=A2+7
Fill that formula down by dragging the yellow dot, then "remove the formula" by typing command-c to copy, then choosing Edit > Paste Formula Results.
Delete the row with 30Dec.
Done!
SG
SunCityLady wrote:
couldn't figure out how to autofill only the weekdays.
You can do this without a complex formula with conditionals. It can be as simple as this:
Fill in the first 5 workdays (in this example 30Dec through 3Jan). You can do that by filling in 30Dec, then dragging the yellow dot down 4 rows.
Then in the next cell below that put this formula, referring to the cell with the first Monday (in this example A2):
=A2+7
Fill that formula down by dragging the yellow dot, then "remove the formula" by typing command-c to copy, then choosing Edit > Paste Formula Results.
Delete the row with 30Dec.
Done!
SG
Hi SCL,
Numbers will fill sequential dates, or an arithmetic sequence of dates (every second or every third, etc) requiring only the first date
or the first and second dates in the series to be entered. The rest get filled automatically when you select the first (for a fill stepping the dates by one day) or the first two (for a series stepping the dates by the difference between the two 'seed' dates.
For the case you describe, you'll need a formula or a script.
Using a formula to fill column A, starting at A2, with the workdays and exclude the weekends (Saturday and Sunday, you could do the following:
IF(WEEKDAY(A2,1)=6,A2+3,A2+1)
In the example, column B contains a formula that returns the DAYNAME of each date in the list in column A. This is included only to illustrate the results from the formula in column A.
Regards,
Barry
Barry......thank you so much for the information. It was perfect and worked great. I am extremely comfortable with Excel and have used it for more than 25 years. About 5 years ago I changed over to Apple everything and have been slowly converting my Excel spreadsheets to Numbers in preparation for downloading Catalina since my MS Office products are a 2014 version and won't be compatible with the 64-bit operating system. Generally it was easy to convert except I couldn't figure out how to autofill only the weekdays. It was easy to autofill all of the dates but I really didn't want to have to manually go in and delete the weekend dates! This will help so much. I love Numbers even though there is a little bit of a learning curve for some features. I will never go back to a Windows platform but to be honest this one function in Excel is over the top simple to do compared to Numbers. But now that you have helped me to understand how to do it that won't matter. Thanks again!
Hi SG,
Nice. And Simple.
Regards,
Barry
Autofill in Numbers
