• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for removing Weekend dates from existing list of dates

rshetty_79

New Member
I am preparing test data for my testing...where in I have to play with dates

I prepared the data with dates

and used the TODAY() function with + and - thing

to arrive at dates

the problem now is that I want to remove the weekend days, sat and sun

do you know of any formula

which merges with my Today function

to remove sat and sun dates. As the data is of around 3 months time period.
 
Let's say you have in A2:

=TODAY()

Formula in A3 is:

=WORKDAY(A3,1)

Copy down as needed. Formula will not give any weekend. Note you can also give a optional 3rd arguement to list any holidays you want to skip.


PS. If you really want a single formula, I suppose you could do:

=WORKDAY(TODAY(),ROW(A1)-1)

and drag that down.
 
Hi Luke, Thanks for the reply! My apologies, I should have elaborated myself.


Below is the way I have created my data.

Cell A1= Today()-90

Cell A2= Today()-89

.

.

.

Cell A90= Today()


In such a case, how do I go about removing Weekends?


Regards,

RS
 
Hi, rshetty!


I think your last example line should have been either:

Cell A90= Today()-1

or:

Cell A91= Today()


Regards!
 
Hi, rshetty!


Back to your problem, if we could assume that the first date at A1 cell is valid, so not on weekend, you can type in A2 and copy down as needed:

=A1+1+SI(DIASEM(A1)>5;2;0) -----> in english: =A1+1+IF(WEEKDAY(A1)>5,2,0)

if your weekend is on Saturday and Sunday, otherwise check for proper values (actually 6&7).


Regards!
 
Back
Top