+ Reply to Thread
Results 1 to 5 of 5

Dates in gant/pivot table?

  1. #1
    Registered User
    Join Date
    06-25-2014
    Location
    Elstree
    MS-Off Ver
    2010
    Posts
    8

    Dates in gant/pivot table?

    Hi, I am trying to create a chart/report that shows me the following information

    I have for example suppliers 1 2 3 4 etc..
    they are booked on 1/7/2014 for 3 days.
    All i have is the start date.

    How do make a formula to assume they are also booked on 2/7/2014 and 3/7/2014

    Ie
    supplier 1 start 1/7 booked for 3 days
    supplier 2 start 2/7 booked for 2 days
    supplier 3 start 3/7 booked for 10 days

    I am thinking the booked value is = 1 and not booked = 0
    this way can see how many suppliers are booked on 1 day.. easy on the above, but in lots of lines- excel!

    thanks for help

  2. #2
    Registered User
    Join Date
    06-25-2014
    Location
    Elstree
    MS-Off Ver
    2010
    Posts
    8

    Re: Dates in gant/pivot table?

    I've attached an example document -- the yellow is the part I am trying to work out how to do... as I have the start date, end date
    thanks
    Attached Files Attached Files

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Dates in gant/pivot table?

    Formula I used is

    IF(AND(date >= start_date, date <= end_date), 1, 0)

    And then I colored with conditional formatting; anything greater than 0.5 gets the yellow colors. You can adjust that.

    (Plus a table of dates and COUNTIFS that delivers the same thing).

    I also slapped a stacked bar chart in with a date axis to show another way to display Gantt charts.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-25-2014
    Location
    Elstree
    MS-Off Ver
    2010
    Posts
    8

    Re: Dates in gant/pivot table?

    thats awsome ben! thanks...
    have been playing a bit with it.. just need to work out how to count for type of skilled person and if its a quote or reservation

  5. #5
    Registered User
    Join Date
    06-25-2014
    Location
    Elstree
    MS-Off Ver
    2010
    Posts
    8

    Re: Dates in gant/pivot table?

    Hi
    I am trying to make the yellow field, subtotal with a critera, i am a little lost ... , i need is department and status, invoice etc..
    trying count if etc, but probably wrong...

    Thanks all in advance
    Attached Files Attached Files

+ 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. Select Multiple Dates In Report Filter Pivot Table But Dates Are Not Static
    By biasedobserver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2014, 03:38 PM
  2. Replies: 0
    Last Post: 05-18-2013, 03:29 AM
  3. Calculate orders past agreed dates in pivot table and averages in pivot tables
    By applesandpears in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-18-2012, 05:26 PM
  4. Pivot Group Dates - how to remove start and end dates from table
    By markoloughlin in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-21-2012, 04:19 AM
  5. Pivot table dates
    By Ozwilly in forum Excel General
    Replies: 3
    Last Post: 05-24-2012, 08:24 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