auto fill special

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,700
Office Version
  1. 365
Platform
  1. Windows
Hi I have dates like the one below
In the second column, I entered in B4 =1 (first friday) and then B7 = 2 (first Monday), now I want to auto fill that so the next Friday will be 3 and Next monday will 4 and then Next friday is 5 and next monday is 6

So how can I do that automacially? Thank you so much

Tue, 01-Sep-20​
Wed, 02-Sep-20​
Thu, 03-Sep-20​
Fri, 04-Sep-20​
1​
Sat, 05-Sep-20​
Sun, 06-Sep-20​
Mon, 07-Sep-20​
2​
Tue, 08-Sep-20​
Wed, 09-Sep-20​
Thu, 10-Sep-20​
Fri, 11-Sep-20​
3​
Sat, 12-Sep-20​
Sun, 13-Sep-20​
Mon, 14-Sep-20​
4​
Tue, 15-Sep-20​
Wed, 16-Sep-20​
Thu, 17-Sep-20​
Fri, 18-Sep-20​
5​
Sat, 19-Sep-20​
Sun, 20-Sep-20​
Mon, 21-Sep-20​
6​
Tue, 22-Sep-20​
Wed, 23-Sep-20​
Thu, 24-Sep-20​
Fri, 25-Sep-20​
Sat, 26-Sep-20​
Sun, 27-Sep-20​
Mon, 28-Sep-20​
Tue, 29-Sep-20​
Wed, 30-Sep-20​
Thu, 01-Oct-20​
Fri, 02-Oct-20​
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,022
Office Version
  1. 365
Platform
  1. Windows
Place this formula in cell B1:
=IF(OR(WEEKDAY(A1)=2,WEEKDAY(A1)=6),1,"")

Then place this one in B2, and copy down for all rows:
=IF(OR(WEEKDAY(A2)=2,WEEKDAY(A2)=6),MAX($B$1:$B1)+1,"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,869
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
AB
1
201/09/2020 
302/09/2020 
403/09/2020 
504/09/20201
605/09/2020 
706/09/2020 
807/09/20202
908/09/2020 
1009/09/2020 
1110/09/2020 
1211/09/20203
1312/09/2020 
1413/09/2020 
1514/09/20204
1615/09/2020 
1716/09/2020 
1817/09/2020 
1918/09/20205
2019/09/2020 
2120/09/2020 
2221/09/20206
2322/09/2020 
2423/09/2020 
2524/09/2020 
2625/09/20207
2726/09/2020 
2827/09/2020 
2928/09/20208
3029/09/2020 
3130/09/2020 
Main
Cell Formulas
RangeFormula
B2:B31B2=IF(OR(WEEKDAY(A2,2)={5,1}),COUNT(B$1:B1)+1,"")
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,700
Office Version
  1. 365
Platform
  1. Windows
That is so awesome. Thank you very much both.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,869
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,154
Messages
5,640,447
Members
417,143
Latest member
boukadidanizar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top