+ Reply to Thread
Results 1 to 4 of 4

Project Management Excel Sheet - Auto Fill Date Fields

  1. #1
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88

    Project Management Excel Sheet - Auto Fill Date Fields

    Hi guys,

    I am attempting to create an Excel sheet for managing a project.

    I would like to plan the project on one tab (lets call the tab 'Project Planning'), with 5 categories/colums: #, Task, Start Date, End Date and Duration (duration is calculate by End Date-Start Date' + 1 (I don't want the difference, I want the number of total days)).

    On the next tab I would like the page to be setup in a calendar view, which each separated month and individual days. The data entered on the first tab will automatically fill in the appropiate cells of the date set. I don't know if this is too much to ask, or even possible, but it would be great if new lines appeared automatically for when more tasks were added on the first tab.

    I have no idea if this is even possible.

    I am an Ok Excel user who can follow simple instructions. I would preefer to avoid VBA and all that jazz, Conditional Formatting is hopefully the answer.

    I've manually drawn what I am talking (about and what I want) and screenshotted an example and attached it (the thread asked me to use a URL, I have no idea if the image works).

    I have added a screenshot od a manual drawing I did (what I want the finished piece to look like).

    Thanks in advance,

    Danny
    Attached Images Attached Images

  2. #2
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Project Management Excel Sheet - Auto Fill Date Fields

    You didn't include a copy of your Workbook so I mocked you up a quick example of how this can be done.

    Please see the attached Workbook.

    The formula in C9 checks the date range specified in the Project Planning Table against the one above it, if it's in range an "x" is returned.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Each cell has Conditional Formatting to look for an "x", if it's "x" then it changes the Colour and the Background of the cell to Red.

    Can you work with that?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 11 - MS Office 365
    Posts
    88

    Re: Project Management Excel Sheet - Auto Fill Date Fields

    Hey Paul,

    Thank you so much for getting back to me!

    The file you whipped up is great, but a little too simple for what I want.

    I have found a great template which I am going to base my system on (attached).

    However, I have a new problem, which I hope you can help me with!!!:

    The date range on the calendar skips weekends. Cell J10 makes reference to CZ2. CZ2 must be have something to do with this. Note - don't delete CZ2. I did this and it disabled the scroll feature on the dated bar graph.

    Each cell after J10 uses the formula "=WORKDAY(J10,1)". This formual is great, but I don't want the weekends to be excluded.

    I'm sure you are able to do this in no time at all

    Thanks again.

    Danny
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Project Management Excel Sheet - Auto Fill Date Fields

    If you don't want to use WORKDAY then simply change J10 to

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And then K10 to;

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And drag it along.

    Think that should do it?

+ 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-21-2018, 10:46 AM
  2. [SOLVED] Project Management sheet - advanced list problem (Repost)
    By Ztv in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-08-2013, 02:58 PM
  3. [SOLVED] Calendar that auto-populates from a project management tab
    By legaai in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-21-2013, 01:08 PM
  4. Using EXCEL for Project Management
    By maximpinto in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2010, 04:36 AM
  5. Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS!
    By PSSSD in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2006, 04:35 PM
  6. Replies: 0
    Last Post: 07-10-2006, 03:29 AM
  7. [SOLVED] Project Management - Start & End Date Calculations
    By Jean McC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-24-2006, 06:10 AM

Tags for this Thread

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