+ Reply to Thread
Results 1 to 14 of 14

Counting projects on monthly basis considering project stages

  1. #1
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    365 Pro
    Posts
    7

    Counting projects on monthly basis considering project stages

    Hi,

    I am trying to develop a model which can give me the count of projects on a monthly basis considering the status / stage that they are in.

    Definition Definition ITT & Approvals ITT & Approvals Execution Execution
    Project No. Project Name Start Date End Date Start Date End Date Start Date End Date
    20C001C Project 1 17/02/2020 29/05/2020 01/06/2020 17/07/2020 31/07/2020 08/10/2020
    20C002C Project 2 17/02/2020 10/07/2020 13/07/2020 28/08/2020 14/09/2020 20/11/2020
    20C003C Project 3 20/07/2020 11/09/2020 14/09/2020 23/10/2020 05/10/2020 27/11/2020
    20C004C Project 4 02/03/2020 19/06/2020 22/06/2020 31/07/2020 12/08/2019 30/10/2020
    20C005C Project 5 14/04/2020 27/05/2020 28/05/2020 01/07/2020 02/07/2020 10/09/2020
    20C006C Project 6 06/05/2020 17/06/2020 18/06/2020 22/07/2020 23/07/2020 01/10/2020
    20C007C Project 7 28/05/2020 08/07/2020 09/07/2020 12/08/2020 13/08/2020 22/10/2020
    20C008C Project 8 18/06/2020 29/07/2020 30/07/2020 03/09/2020 04/09/2020 12/11/2020
    20C009C Project 9 02/11/2020 11/12/2020 14/12/2020 25/01/2021 26/01/2021 07/04/2021
    20C010C Project 10 20/07/2020 28/08/2020 01/09/2020 05/10/2020 06/10/2020 14/12/2020


    Expected Output
    Not started Definition ITT & Approvals Execution Complete
    31/01/2020 2 4 1 2 1
    28/02/2020 2 2 2 3 1
    31/03/2020 1 4 2 3
    30/04/2020
    31/05/2020
    30/06/2020
    31/07/2020
    31/08/2020
    30/09/2020
    31/10/2020
    30/11/2020
    31/12/2020


    Count active project within the defined month start date to end date.
    If Definition date is not reached, the project has not started.
    If Execution date has passed, the project is completed. If blank, the project is in execution
    The words 'Definition', 'ITT & Approvals' and 'Execution' are the titles within an MS Project file. The data is first exported into excel to arrange data as shown above.

    Any suggestions on how to achieve this?

    Edit1: Changed 2018 to 2020 for the output. Sorry, I am unable to attach the excel into this post for some reason. So, I've posted the data as it is. Let me know if there is a better way to share the excel sample sheet I've prepared.
    Edit2: Excel attached! Thanks AliGW
    Attached Files Attached Files
    Last edited by optimushunk; 01-09-2020 at 08:40 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting projects on monthly basis considering project stages

    Welcome to the forum.

    The instructions about uploading sample workbooks are at the top of the page.
    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.

  3. #3
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Counting projects on monthly basis considering project stages

    Thanks AliGW. I've attached the excel in the first post. I tried using he attachment option in the formatting text box and it wouldn't let me attached. Thought it was because I was a new user . All good now.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting projects on monthly basis considering project stages

    No - it just doesn't work.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting projects on monthly basis considering project stages

    You forgot expected outcomes. Please manually complete a few rows of data and reattach the workbook.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    18
    Expected Output
    19
    Not started Definition ITT & Approvals Execution Complete
    20
    31/01/2020
    21
    29/02/2020
    22
    31/03/2020
    23
    30/04/2020
    24
    31/05/2020
    25
    30/06/2020
    26
    31/07/2020
    27
    31/08/2020
    28
    30/09/2020
    29
    31/10/2020
    30
    30/11/2020
    31
    31/12/2020
    Sheet: Sheet1

  6. #6
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Counting projects on monthly basis considering project stages

    Done. Updated the spreadsheet with expected output. Thanks!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting projects on monthly basis considering project stages

    In C20 copied down:

    =COUNTIF($C$3:$C$12,">"&A20)

    In D20 copied down:

    =COUNTIF($C$3:$C$12,"<="&A20)

    and so on ...

  8. #8
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Counting projects on monthly basis considering project stages

    Quote Originally Posted by AliGW View Post
    In C20 copied down:

    =COUNTIF($C$3:$C$12,">"&A20)
    I believe this is for B20?

    In D20 copied down:

    =COUNTIF($C$3:$C$12,"<="&A20)

    and so on ...
    This gives me count of all the projects that have passed the specified Definition Start Date but does not exclude the projects that have moved into the next stages?

    I suppose, I could use the following for the F20. However, when the cell H12 is left blank, the formula considers that the date is less than the defined date in cell A31.

    =COUNTIF($H$3:$H$12,"<="&A20)

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting projects on monthly basis considering project stages

    Yes, first one for B20 and so on - sorry.

    As for the rest, I wasn't clear on the requirement. I have run out of time today - sorry.
    Last edited by AliGW; 01-09-2020 at 09:25 AM.

  10. #10
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Counting projects on monthly basis considering project stages

    Oh okay. Thanks for everything so far

    Bit tricky to explain what I am trying to achieve. But here I try again:

    I want to know the count of projects considering the different stages of the project for a given timeframe.

    So for the output of 31/12/2020 we should get
    Not started: 0 (Because earlier definition dates are present)
    Definition: 0 (Because all project definition dates have an end date prior to 31/12/2020)
    ITT & Approvals: 1 (Because project 20C009C starts from 14/12/2020 and runs to 25/02/2021. Rest have ended prior to this date)
    Execution: 1 (Project 20C010C has no end date)
    Completed: 8 (Projects 20C001C to 20C008C have finished before 31/12/2020. 20C009C has yet to complete its ITT & Approvals and 20C010C has no end date)

    Total count of project stays 10 for every row. There wouldn't be any doubling up.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting projects on monthly basis considering project stages

    Maybe this will get you a bit closer. In B20 copied down:

    =COUNTIF(C$3:C$12,">"&$A20)

    In C20 copied across to E20 and down:

    =COUNTIF(D$3:D$12,">"&$A20)-SUM($B20:B20)

    In F20 copied down:

    =COUNTIF(H$3:H$12,"<="&$A20)

  12. #12
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Counting projects on monthly basis considering project stages

    Getting closer I suppose

    Attached is the latest version.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    365 Pro
    Posts
    7

    Re: Counting projects on monthly basis considering project stages

    Any more ideas that we can explore?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting projects on monthly basis considering project stages

    It would help (me) if you filled in the entire grid manually so that I know what we are aiming for.

+ 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: 1
    Last Post: 08-08-2019, 10:23 AM
  2. [SOLVED] Adding totals of projects in different stages by a VBA search function into dashboard
    By Raylou in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 07-02-2018, 01:13 AM
  3. Move rows up when project changes stages
    By Ian Frost in forum Excel General
    Replies: 0
    Last Post: 08-30-2017, 09:12 AM
  4. Replies: 8
    Last Post: 08-09-2012, 02:21 PM
  5. [SOLVED] Insert Blank Rows between sorted projects, Subtotal project to the right of final project.
    By ZAC7 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-07-2012, 04:08 AM
  6. Replies: 5
    Last Post: 08-03-2012, 01:43 AM
  7. Old Projects in Project Window
    By kwiklearner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2006, 01:40 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