Remove value from list using loop

[hyattj]

In general I want to take a future single date i.e. (Wednesday, December 16, 2015) and create a list of dates dating back to the current date, that excludes the weekends. I'm sure there is a very easy way to do this using macros but I am still not very familiar with excel syntax. What i have created so far is a list that dates backwards but has weekends included, which is in column G. Then i created a list in column J that searches the text of the list in column G for either "Saturday" or "Sunday" and writes the word remove i.e.

IF(OR(TEXT(G9,"dddd")="Saturday",TEXT(G9,"dddd")="Sunday")=TRUE,"remove",G9)

Unfortunately i just want to remove that value in the list but do not know how. I have coded using python before and know this is extremely easy using a loop, again I just dont know the syntax of excel yet. Can someone help with the code or can it be done with just plain formula's?

date forum example.xlsx

-Jake

[MrMcLee]

Try this.

Please Login or Register  to view this content.

[MrMcLee]

Nevermind, I didn't see your example before I did that. I fixed it, but as soon as it deleted a line, it screwed up the rest of the sheet because of the formula you used. I'll try this later. I think a macro might be unnecessary.

[MrMcLee]

date forum example.xlsx

Here you go, no macro needed.

[AndyLitch]

date forum example.xlsm

Try that.. The code is fairly simple and easy enough to modify

[Haseeb Avarakkan]

Hello Hyatt;

Take a look at WORKDAY function, which will exclude weekends; also HOLIDAYS if you have.

G9, enter the start date, then in G10

=WORKDAY(G$9,-ROWS(G$10:G10),Holidays)

Copy down as needed. If you have any holidays enter those in a range & use that range in Holidays area. If you don't have delete those part.

[JBeaucaire]

Jake, When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)


Your duplicate posts did not comply with Rule 5 of our forum rules. Your duplicate threads were merged, somebody's time got wasted here.

Threads Merged.

[hyattj]

Sorry about the duplicate thing but I got it to work so Thank you everyone who posted!

-Jake