1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 - Start Date and End date question

Discussion in 'Business Applications' started by jmstampe, Feb 18, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. jmstampe

    jmstampe Thread Starter

    Joined:
    Feb 17, 2012
    Messages:
    8
    So I have this excel spreadsheet and an example of it basically looks like this:

    Project Phase Person Capacity Start Date End Date
    A Design 1 25% 1/15 3/16
    A Model 1 25% 3/17 5/11
    A Design 2 25% 1/15 3/16
    A Model 2 25% 3/17 5/11

    I'm looking to track a monthly outlook of capacity % over time for each person (in a pivot table), but I'm unable to get it exactly the way it should be. Essentially I would like it to look like this below:

    Dates
    Person Jan Feb Mar Apr May
    1 25% 25% 25% 25% 25%
    2 25% 25% 25%

    (Sorry, the text keeps left aligning so person 2 should actually have 25% under Mar, Apr, and May.)

    Is this even possible, either in a pivot table, or even using formulas?
     
  2. Sponsor

  3. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    63,338
    so you are trying to make a list of the dates based on the start and end

    so if started in jan and completed in Nov
    then you want to see
    Code:
    Person  Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov 
    1        25%  25%  25%   25%   25%    25%  25%   25%    25%   25%  25%
    
    correct?

    to keep the space - use the code tag

    so put a [
    then type the word
    code
    then put a ]
    at the beginning and then
    then
    put a [/
    then type the word
    code
    then put a ]
    at the end
     
  4. jmstampe

    jmstampe Thread Starter

    Joined:
    Feb 17, 2012
    Messages:
    8
    Ah thanks for showing me that. I'm new to the site so I'm still trying to figure it out.

    But yes that's what I'm trying to do. It would basically be like a resource loading tool for seeing % utilization over time by each person.
     
  5. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    63,338
    its quite straight forward to do the end date or the start date in a pivot - but will need a lot more to include all the months in between - not sure how to do this - so hopefully someone else will reply - probably need a macro to add the extra month
     
  6. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    Why don't you post a sample sheet with dummy data, it's easier to see than try picturing it from words
     
  7. jmstampe

    jmstampe Thread Starter

    Joined:
    Feb 17, 2012
    Messages:
    8
    Sure,

    Let me know if this works or helps.
     

    Attached Files:

  8. romialbert

    romialbert

    Joined:
    Feb 12, 2012
    Messages:
    15
    Hi,

    I wanna give you an alternative solution. But i dont know how to attach the file because i'm new for this forum. Can you tell me how to attach files?
     
  9. romialbert

    romialbert

    Joined:
    Feb 12, 2012
    Messages:
    15
    Please find the attachment.

    Somehow, i've managed to get the actual date of works (looking in some forum) considering the period of works ('actual days of work' table
    Then see the 'capacity' table, hope it can answer your question.

    Regards.
     

    Attached Files:

  10. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    I like the approach, I would however suggest that the data be split into three sheets:
    Table 1 on one sheet
    table 2 on another sheet
    Pivot table on a sthrid sheet
    This will make the tables expandable and easire to manege, also the formula's would not have to be changed if let's say another person was added or whatever.
    But this is excellent. (y)
     
  11. romialbert

    romialbert

    Joined:
    Feb 12, 2012
    Messages:
    15
    well, thanks =D. yes, it should be split into 3 sheets.
    i found that formula long time ago in other forum (i forgot) and i still use it until now.
    hope it will be useful for anyone.
     
  12. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    I used the sheet Romialbert posted and put my idea in Sheet3

    The columns for the months i filled with actual date, 1-1-2012 through 12-1-2012 and formatted to show the abbreviaded month name.

    The formula is just to check if the month(startdate) >= month(G3) and month(enddate)>=month(g3) if this is the case the capacity is 25% (value in D) or else ""

    You can hide the capacity start and enddates and the table remains.

    OPivot table or otherwise is easier to achiev via macro's

    You could maybe apply the list function to this table ?
     

    Attached Files:

  13. romialbert

    romialbert

    Joined:
    Feb 12, 2012
    Messages:
    15
    wow .. u made it more simple .. it's like magic !
     
  14. jmstampe

    jmstampe Thread Starter

    Joined:
    Feb 17, 2012
    Messages:
    8
    Wow, this is great stuff and may work. I may need get it from monthly to bi-weekly though for a more detailed view of utilization. I'm assuming biweekly can be done exactly the same way?

    I'll try these out, when I get back into work tomorrow, and keep you posted. Also, any other ideas are still great as I like seeing how things can be done from many different angles. Thank you so much for all the help.
     
  15. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,351
    The trigger was Romialbert's sheet, which if you need days is better, my version is just to check if the perosn is in the period of time.
    Bi weekly will need moore coding
     
  16. jmstampe

    jmstampe Thread Starter

    Joined:
    Feb 17, 2012
    Messages:
    8
    So I tried out both methods, using a biweekly comparison and I was able to get it to work out so far without any issues I'm seeing. I want to thank you all so much for the help.
     
  17. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1041709