+ Reply to Thread
Results 1 to 3 of 3

Formula for status update based on date

  1. #1
    Registered User
    Join Date
    12-21-2018
    Location
    Georgia, USA
    MS-Off Ver
    2013
    Posts
    1

    Formula for status update based on date

    I have a spreadsheet that shows work tasks that need to be accomplished. Columns A, B, C, and D are as follows: "Task Status", "Date", "Task Details", and "Task Completed?". My boss would like to make the "Status" column update automaticaly. Ideally I would like the spreadsheet to work by using the date. When the user writes down the task they also put an estimated completion date in the "Date" column. If the current date is before the date listed in the "Date" column, the "Status" column will be yellow. If the current date is after the date listed in the "Date" column, the "status" column will be red. If the task is completed, the user marks that in the "Task Completed?" column and the "Status" column becomes green.

    To do this I figured I would be able to use conditional formating by using the following formula:

    =IF(B3-TODAY()>1,6,4)

    So if the date entered in the "Date" column is 19 DEC 2018, the formula will put a 4 in the "Status" column because its currently the 21st and that would mean that the task is past due. In the "Status" column I used conditional formating so any number populated in that field that is lower than 5 will produce the red circle with an x in it.

    Next I tried to use a simple "IF" statement that made it to where when you put something in the "Task Completed?" column it would populate a 10 in the "Status" block. This is the formula I tried to use:

    =IF(B3-TODAY()>1,6,4,IF(G3="X",10,))

    I keep getting "You've entered too many arguments for this function"

    Does anyone have any suggestions on how I could possibly accomplish my goal here? I am by no means an Excel guru and would love to learn anything you could teach me. Thanks!

    -Steven
    Last edited by AliGW; 12-22-2018 at 04:17 AM. Reason: Title amended by moderator

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,232

    Re: Help with complicated task.

    .
    Here is one way:


    A
    B
    C
    D
    1
    Task Status
    Date
    Task Details
    Task Completed
    2
    3
    Formulas In A2 Cond. Format Color
    4
    5
    =NOT(B2<>"") White
    6
    =(AND(D2="",B2>NOW())) Yellow
    7
    =(AND(D2="",B2<NOW())) Red
    8
    =(D2<>"") Green

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Help with complicated task.

    Welcome to the forum!

    Please note for future reference that your thread title is not sufficient to satisfy this forum's rules and should be changed if you require further help in this thread. (EDIT: I have changed it for you this time).

    • Use concise, accurate thread titles.
    • Your post title should describe your problem, not your anticipated solution.
    • Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
    • Responding to a request to change your thread title by doing so is mandatory.

    To change a title go to your first post, click EDIT then Go Advanced and change your title.
    Last edited by AliGW; 12-22-2018 at 04:16 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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] Pivot Chart: Task spend over time, +comparison w/ Task Budget
    By mike_302 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-28-2018, 04:21 PM
  2. Complicated task of avg, rank, and sorting
    By visguy in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-07-2015, 04:23 PM
  3. export excel list of task in custom outlook task 2010.
    By maxseal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-22-2014, 03:51 PM
  4. Replies: 0
    Last Post: 11-17-2013, 10:21 AM
  5. [SOLVED] IF contingent task closed, THEN change formatting of dependent task cell
    By tek_9 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-29-2012, 08:40 PM
  6. Previous Task and Next Task buttons aren't working
    By top.C.Crets in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-07-2012, 08:27 AM
  7. dialog box for active cell task to speed repeditive task
    By Todd F. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-09-2005, 10:05 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