+ Reply to Thread
Results 1 to 10 of 10

Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calculate

  1. #1
    Registered User
    Join Date
    05-24-2014
    Posts
    34

    Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calculate

    Bare with me, I am not sure the best way to word this.

    Currently column I(uncompleted) calculates days between current date and whichever date is later between columns E(Scheduled) and F(Arrived).
    I would like column I(uncompleted) to continue doing this unless there is a date in column K(Notification).
    Once there is a Date in column K(Notification) I would like column I(uncompleted) to become blank and then I would like column J(completed) to calculate days between column K(Notification) and the later date between Columns E(Scheduled) and F(Arrived).

    Attached is an example that I hope will help you all to understand what I am wanting to do.

    Let me know if you need more info to help clarify it more.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    In column I:

    =IF(K5="",TODAY()-MAX(E5:F5),"")

    In column J:

    =IF(K5<>"",K5-MAX(E5:F5),"")
    If I've helped U pls click on d *Add Reputation

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    in I
    =IF(K5="", TODAY()-MAX(E5:F5), "")
    in J
    =IF(I5="", K5-MAX(E5:F5),"")
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    05-24-2014
    Posts
    34

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    Thank you both, those work great!

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,731

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    you are welcome - thanks for the rep

  6. #6
    Registered User
    Join Date
    05-24-2014
    Posts
    34

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    I know this is marked solved already, but is there an easy option to add to the formulas where when if there isn't any data entered yet, column I and J will be blank or display a zero?
    I can make a new post if needed, just thought might be easier here since you two understand the current formula and what am I trying to do.

  7. #7
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    assuming that you mean when there are no values in B:G. then use these formulas:

    in Column i

    =IF(COUNTA(B5:H5)=0,"",IF(K5="",TODAY()-MAX(E5:F5),""))

    in column J

    =IF(COUNTA(B5:H5)=0,"",IF(K5<>"",K5-MAX(E5:F5),""))

  8. #8
    Registered User
    Join Date
    05-24-2014
    Posts
    34

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    Attached is an example showing data in the formula fields while the input data fields are blank.
    I tried you suggested new formula but it was displaying the same thing.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    610

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    If the cells highlighted in pink are the input cells then:

    in column i:

    =IF(COUNTA(A5:E5)=0,"",IF(H5="",TODAY()-MAX(D5:E5),""))

    in column j:

    =IF(H5<>"",H5-MAX(D5:E5),"")

  10. #10
    Registered User
    Join Date
    05-24-2014
    Posts
    34

    Re: Formula: If cell 3 is blank have cell 1 calculate, if cell 3 filled have cell 2 calcul

    Thank you so much... Your a life saver!

+ 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. [SOLVED] IF Cell is blank, calculate, if cell isn't blank, leave blank
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2015, 03:54 PM
  2. Calculate differnce between 2 dates, but if 1 date cell is blank leave cell blank
    By Vicious00013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2014, 11:31 PM
  3. Replies: 0
    Last Post: 08-14-2012, 10:42 AM
  4. Formula to calculate if cell A* is the first non-blank, then...
    By galaxy_madness in forum Excel General
    Replies: 7
    Last Post: 11-25-2011, 04:30 PM
  5. Move filled cell to right and insert blank cell
    By Chemistification in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-18-2011, 08:39 AM
  6. Find Last Filled Cell In Column And Calculate
    By sratkins in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-06-2011, 01:50 PM
  7. Replies: 9
    Last Post: 03-23-2010, 02:07 PM

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