+ Reply to Thread
Results 1 to 11 of 11

Daily Task Report has End Date, then the status should change automatically to Completed

  1. #1
    Forum Contributor
    Join Date
    05-24-2015
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    333

    Daily Task Report has End Date, then the status should change automatically to Completed

    Hello,

    I am new with excel. I have a daily task report sheet attached. If user enters a date in the Task End Date in Collumn L, then the Status in collumn N should change automatically to Completed.

    I also need help to create weekly pivot table report which can be shared showing tasks, status, total number of tasks, etc.

    The required fields (highlighted in yellow) that needs to be displayed in the pivot table are:
    1. Task Description
    2. Task classification
    3. No of Hours
    4. Status
    5. Remarks
    6. Finally the total number of Tasks should be displayed below. This is the total number of values in Task description. Say there are 5 tasks, then total should be displayed as 5. Note, the report is to show weekly status

    Appreciate your help and expertise.

    Thanks and Regards,
    Santosh
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    First change the datavalidation on column N

    N5 =if(L5="","In progress","Completed")
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    05-24-2015
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    333

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    Can it be implemented as follows:

    If user enters date in Task End Date Column, then the data validation list in Status Collumn should only show Completed option which user can select. The Other options In progress and in review should not be available for selection

    If there is no date in Task End date Collumn, then the data validation list should show the options In Progress or In Review as options which can be selected by user. In this case Completed option should not be available for selection

    Thanks,
    Santosh

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    In that case I should define, when the option is In Progress and when the option is In Review.

    I should implement that criteria in the if-formula.

  5. #5
    Forum Contributor
    Join Date
    05-24-2015
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    333

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    Yes, if there is date in Task End Date collumn, then the dropdown list in Status Collumn should have only Completed value which user can select

    If no date, then dropdown list should have In progress and In review to select.

    There is not need of automatic entry. User will be selecting completed or In progress or in review manually

    Regards,
    santosh

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    See my #4.

    If there is a need to manualy select the cells, what is the problem having a third option (I don't get it).

  7. #7
    Forum Contributor
    Join Date
    05-24-2015
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    333

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    In Collumn N with name Status there are following values:
    1. In Progress
    2. On Hold
    3. In Review
    4. Incorporating Review Comments
    5. Completed

    The problem is users sometimes put an End Date and keep the status as In Progress, On Hold, etc. We want to stop that. If user enters End Date then only Completed option should be available for them to select.

    If there is no end date then the below values should be available in Collumn N:
    1. In Progress
    2. On Hold
    3. In Review
    4. Incorporating Review Comments

    Regards,

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    see the attached file
    I changed your name manager Status as
    =Sheet2!$D$2:INDEX(Sheet2!$D$2:$D$6,IF('KM Task List'!$L5>0,5,4))
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  9. #9
    Forum Contributor
    Join Date
    05-24-2015
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    333

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    It is working fine when there is no date in the Task End Date Collumn

    But when there is date in the Task End Date Collumn, I can see that all the values are avialable for selection. Can you please make only Completed value available when there is date in the Task End Date Collumn. The user should have only option to select Completed when there is date available in the Task End Date Collumn

    Regards,
    Santosh

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    see the attached file
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    05-24-2015
    Location
    Dubai
    MS-Off Ver
    2010
    Posts
    333

    Re: Daily Task Report has End Date, then the status should change automatically to Complet

    Thanks a lot, it really works. Can you please provide me the macro or formula how you achieved this.

    Regards,
    Santosh

+ 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. Replies: 2
    Last Post: 04-15-2014, 03:23 AM
  2. Daily Status Report
    By peter5050 in forum Excel General
    Replies: 1
    Last Post: 04-19-2013, 01:25 AM
  3. How to automatically change Leave status to shortform in a drop down list?
    By fuzzy1203 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2012, 01:12 AM
  4. Count Unique Task IDs Completed within a Date Range
    By Spoklahoma in forum Excel General
    Replies: 6
    Last Post: 12-11-2011, 01:50 PM
  5. Printing daily report automatically
    By Brent Twede in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2008, 06:23 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