Copy down unique date formula

benfitz

New Member
Joined
Jan 13, 2010
Messages
8
I want to copy a formula down. The data is dates between 02/01/2009 and 14/12/2009 but only want every Wednesday and Saturday. I have put in the first few and trie to copy the formula down but excel just copies the five or so I have put in and pastes that over and over again.

Is what I am trying to do possible and if so how?
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
I've created some random dates and the formula i put in seems to work I've added and formatted the cells in column K for info only

Excel Workbook
IJK
425/06/2009 
503/12/2009
628/03/200928/03/2009Sat
714/06/2009
809/08/2009
902/11/2009
1005/03/2009
1107/12/2009
1210/03/2009
1309/11/2009
1415/05/2009
1502/08/2009
1608/04/200908/04/2009Wed
1728/11/200928/11/2009Sat
1816/05/200916/05/2009Sat
1928/05/2009
2011/07/200911/07/2009Sat
2121/09/2009
2225/06/2009
2308/03/2009
Sheet5
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
I just tried my formula with your date range and it works just fine.......but too large to display on here
 

scottylad2

Well-known Member
Joined
Feb 2, 2009
Messages
1,919
One way to do it, is list all the dates in A:1 and copy down your dates then apply my formula in B1 and copy it down the whole row. next in C1 do =B1 and copy down also and while the column is still highlighted, click on ctrl 1 and open the format menu. Click on Custom and in the box type ddd then ok You should now have the dates in col B and the dates formatted as Sat or Wed in col C. Go to the Data menu, then click on Filter, you will have various dates in there and als a check box for blanks. untick that, and your list will now only be sats and weds....copy and paste to a new location and you'll have your list of weds and sats
 

benfitz

New Member
Joined
Jan 13, 2010
Messages
8
Thank for the help. That worked great. My only problem is I have many entries for each day. How can I filter these further to erase the duplicates and only get one of each wed and sat.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thank for the help. That worked great. My only problem is I have many entries for each day. How can I filter these further to erase the duplicates and only get one of each wed and sat.

One way: Advanced Filter with the Unique records oly option checked.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

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