1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting Start and Finish Dates Based on Grouping Levels

Discussion in 'Ask an Excel Question' started by Excel_User18, Sep 13, 2018.

  1. Excel_User18

    Excel_User18 New Member

    Messages:
    6
    I have an Excel workbook that I copied from Project and formatted based on the Project file. I have inserted a picture below of my Excel workbook. I grouped the rows by their outline level in order for the grouping to match the summary and sub-tasks format in Project. I was wondering if their was a macro I could run that could automatically update the Start and Finish dates (columns E and F below) of summary tasks based on change to those dates in their sub-tasks (as is done in Project). For instance, if I changed the Finish date of "Project Plan Finalized," the Finish date of "Analyze" and "Phase 1" would be automatically updated; I'd like to obviously do the same for Start date too. If anyone has a macro that could update those dates automatically, that would be amazing!

    upload_2018-9-13_14-2-35.png
  2. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Excel_User18
    That kind of macro can create ...
    If You could give exact rules for those needed actions.
    How about those 'Durations'? Do those need to update too?
    Are those E-&F-column information pasted as text or real dates?
    ... and it would be easier for You, if You could upload a sample file.
  3. Excel_User18

    Excel_User18 New Member

    Messages:
    6
    The "Duration" column has a formula that will automatically update based on what is in the adjacent columns ("Start" and "Finish"). Attached is my Excel file.

    Attached Files:

  4. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Excel_User18
    I also wrote: If You could give exact rules for those needed actions.
    ... I didn't find that part...
  5. Excel_User18

    Excel_User18 New Member

    Messages:
    6
    I don't know what you mean by rules. I would like the Start Date and Finish Date of Phase 1 to be captured. For example, if I change a date of a task in Phase 1, anywhere in Phase 1, and now that date is the earliest/latest date in Phase 1, I'd like Phase 1's start and finish dates to reflect that. I'm sorry if I'm not explaining it well.
  6. Excel_User18

    Excel_User18 New Member

    Messages:
    6
    I thought about using MIN and MAX functions, but I don't know if that would work...
  7. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Excel_User18

    The Rule = eg how to do something...

    You wrote one rule like:
    if I changed the Finish date of "Project Plan Finalized," the Finish date of "Analyze" and "Phase 1" would be automatically updated ...
    That is clear! It's possible to automate just like that!

    Next You 'asked something' like:

    I'd like to obviously do the same for Start date too. That is not clear!

    Your #5 reply ... hmm?

    >> You would try to think ...
    1) What would You like to change?
    2) What would happen after that?
    3) How would You do it manually?
    Try to give few clear samples.
    Chirag R Raval likes this.
  8. Excel_User18

    Excel_User18 New Member

    Messages:
    6
    I meant that if I changed the start date of a task under "Phase 1," and it became the earliest date in "Phase 1" (and thus the Start date for "Phase 1"), then I would want the "State" date column of "Phase 1" to change appropriately.
  9. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Excel_User18 ... yes!
    If no answers to questions then, I could only guess...
    If You change any date one day earlier, then do You want ALWAYS that all later that 'Phase' dates would step one day earlier (= no changes with durations) or JUST that changed date? (= one duration would change)
    and same If You change any date one day later.
    And same if that change would be eg 30days...
  10. Excel_User18

    Excel_User18 New Member

    Messages:
    6
    I think we are having a little trouble getting on the same page. Don't worry about it. I appreciate you wanting to help! Have a good one!

Share This Page