# skip row if other cell is sunday using If function

1. ## skip row if other cell is sunday using If function

I have 3 columns, one column having all dates of the month, like 1/10/14, 2/10/14 etc. 2nd column having values like 180, 181 etc. On the third column I need a total of column 2 +1 like that. But if 5/10/14 comes that should be blank and should start adding sum from Monday onwards.

For example:
01-Oct 181
02-Oct 182
03-Oct 183
04-Oct 184
05-Oct
06-Oct 185
07-Oct 186
08-Oct 187
09-Oct 188
10-Oct 189
11-Oct 190
12-Oct
13-Oct 191
14-Oct 192
15-Oct 193

2. ## Re: skip row if other cell is sunday using If function

You can use the Weekday function to find the weekday serial number of a date

1=Sunday
2=Monday
3=Tuesday
....

So, if it is Sunday you want to skip, use this

=TEXT(IF(WEEKDAY(A3)=1,"",A3+B3),"dd-mmm")

3. ## Re: skip row if other cell is sunday using If function

Hi

Hope this is what you meant.

I have hidden a column "A" which basically looks at the date and returns the Day eg "Sunday"

Then using that created an if statement to only calculate the sum when not = to Sunday

4. ## Re: skip row if other cell is sunday using If function

If your dates are in col. A and starts from A1, input 181 in B1 and then try this...
In B2
``Please Login or Register  to view this content.``
and copy down.

Is this what you are trying to achieve?

5. ## Re: skip row if other cell is sunday using If function

Or alternatively use this formula in column c and drag down

=IF(TEXT(A1,"DDD")="Sun","",B1+A1)

6. ## Re: skip row if other cell is sunday using If function

Column A having the dates of the whole month. In Column B I need auto increment of a number say 1 from any number skipping when Sunday's occur.

For example:
01-Oct 181
02-Oct 182
03-Oct 183
04-Oct 184
05-Oct
06-Oct 185
07-Oct 186
08-Oct 187
09-Oct 188
10-Oct 189
11-Oct 190
12-Oct
13-Oct 191
14-Oct 192
15-Oct 193

7. ## Re: skip row if other cell is sunday using If function

If you use the formula suggested in post#4, you will get the following output.

 A B 1 01-Oct 181 2 02-Oct 182 3 03-Oct 183 4 04-Oct 184 5 05-Oct 6 06-Oct 185 7 07-Oct 186 8 08-Oct 187 9 09-Oct 188 10 10-Oct 189 11 11-Oct 190 12 12-Oct 13 13-Oct 191 14 14-Oct 192 15 15-Oct 193 16 17 B1 =181 18 B2 =IF(WEEKDAY(A2)<>1,INDEX(B\$1:B1,MATCH(1E+307,B\$1:B1))+1,"")

8. ## Re: skip row if other cell is sunday using If function

ok I see use this formula in column b

=IF(B2="",B1+1,IF(TEXT(A3,"DDD")<>"Sun",B2+1,""))

if you make sure your dates start A3 and have row 1 blank and in b2 your starting number for eg 180

9. ## Re: skip row if other cell is sunday using If function

Thanks, works great

10. ## Re: skip row if other cell is sunday using If function

Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

11. ## Re: skip row if other cell is sunday using If function

Originally Posted by sktneer
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.
Sorry,

Your answer works perfectly. I could have mentioned it clearly. Sorry missed it.

Thanks very much for your valuable time. Also thanks for the valuable time for every one paid attention to my question

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1