You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Autofill in Numbers

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

Posted on Dec 24, 2019 4:48 PM

Reply
Question marked as Top-ranking reply

Posted on Dec 25, 2019 6:29 AM

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


4 replies
Question marked as Top-ranking reply

Dec 25, 2019 6:29 AM in response to SunCityLady

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


Dec 25, 2019 1:38 AM in response to SunCityLady

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:

  • Enter the starting date in A2
  • in cell A3, type = to open the formula editor, then enter this formula:

IF(WEEKDAY(A2,1)=6,A2+3,A2+1)

  • Fill the formula down the column ad many rows as you need.

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

Dec 25, 2019 5:24 AM in response to 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!

Autofill in Numbers

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