+ Reply to Thread
Results 1 to 11 of 11

skip row if other cell is sunday using If function

  1. #1
    Registered User
    Join Date
    01-11-2013
    Location
    chennai
    MS-Off Ver
    Excel 2010
    Posts
    59

    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. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    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")
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Registered User
    Join Date
    01-04-2013
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2010
    Posts
    66

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

    Hi

    Please see attached sheet

    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
    Attached Files Attached Files

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    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?
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    01-04-2013
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2010
    Posts
    66

    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. #6
    Registered User
    Join Date
    01-11-2013
    Location
    chennai
    MS-Off Ver
    Excel 2010
    Posts
    59

    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. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    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. #8
    Registered User
    Join Date
    01-04-2013
    Location
    Newcastle, England
    MS-Off Ver
    Excel 2010
    Posts
    66

    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. #9
    Registered User
    Join Date
    01-11-2013
    Location
    chennai
    MS-Off Ver
    Excel 2010
    Posts
    59

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

    Thanks, works great

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

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

    Please try to be precise in your replies. Your reply didn't tell us that which solution worked for you.
    Anyways, if that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
    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. #11
    Registered User
    Join Date
    01-11-2013
    Location
    chennai
    MS-Off Ver
    Excel 2010
    Posts
    59

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

    Quote Originally Posted by sktneer View Post
    Please try to be precise in your replies. Your reply didn't tell us that which solution worked for you.
    Anyways, if that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do i make my Countif function skip counting a blank cell
    By Renildrah in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-20-2014, 05:00 AM
  2. date function which did not include sunday
    By Harrold in forum Excel General
    Replies: 12
    Last Post: 11-03-2011, 12:06 PM
  3. Function to Ignore Saturday and Sunday
    By kamalthakur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2008, 11:34 AM
  4. WORKDAY function should take only sunday as non working day
    By irfanch in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2008, 06:00 AM
  5. [SOLVED] Sunday Function
    By Sunday Function in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2006, 03:15 AM

Bookmarks

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