+ Reply to Thread
Results 1 to 4 of 4

How to change colour of a date in a calendar made in a pivot table as per a specific value

  1. #1
    Registered User
    Join Date
    11-27-2013
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    2

    How to change colour of a date in a calendar made in a pivot table as per a specific value

    Hello all!

    I've learned recently that I know nothing about the capabilities of excel. I've managed to get this far alone, but I'm stuck and hope you lovely folks will be able to point me in the right direction.

    I recently offered to help my SO make an easier excel file to track vacation days of his colleagues (the one the company offers was made back in 1995 and is horrendous. They plan on updating it never.) The criteria for this project is to have two worksheets:

    1. A pivot table of the yearly calendar (calendar.jpg and pivot.jpg below)
    2. Data used for the pivot table. (data.jpg below)

    For each day of the month, a max of only 2 people are allowed to take a day off. In an effort to make it easier for his colleagues to determine when they can take time off, my SO would like the days in the calendar to remain "white" if there are two slots available, "yellow" if only one slot is available, and "red" if no slots are available for vacation.

    In my data sheet, he will list their names in "First slot" and "second slot" on the days they select. I have made it that depending on the status of each cell, a number will appear in the corresponding columns "slot 1" and "slot 2", where if blank, the value is "0", if there is text, the value is "1." I totaled these in the "Total" column.

    I am not sure if I'm going the wrong direction, but my thought process is that if the result for a specific date in the "Total" column was "0", then the date would remain "white", if "1", it would be "yellow" and if "2" then "red", meaning no one else can take that day off... but I have no clue how to do that.

    Here is what I have right now:

    calendar.JPG
    pivot.JPG
    data.JPG

    Any help or suggestions would be greatly appreciated.

    ETA: I just realized, I could have just uploaded the excel file (DOH!): Attachment 280561
    Last edited by lmg7; 11-27-2013 at 08:14 PM. Reason: realized I could upload teh xls file too!

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: How to change colour of a date in a calendar made in a pivot table as per a specific v

    First point: it's much quicker and easier to help if you attach a workbook, rather than JPGs. Saves me recreating your data...

    Now. There are several ways to skin this particular cat. I've gone with your original data structure and pivot format, though, to show one possibility. I've added a second Value field [Sum of Total] to the pivot table to give a relative reference containing the count of holiday slots used for each day, then applied some conditional formatting based on that. Then I've hidden those 'helper' columns. It's inelegant, but it works. See attachment.

    CalendarCF.xlsx
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    11-27-2013
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: How to change colour of a date in a calendar made in a pivot table as per a specific v

    I'm so sorry about that. It hadn't occurred to me that I could do so until it was too late

    Thank You! So much... I just couldn't wrap my head around it, but now that I see how it works, total saver and works perfect for what I need.

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: How to change colour of a date in a calendar made in a pivot table as per a specific v

    Great - glad it does what you need.

    You can mark the thread 'solved' now

+ 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. Excel 2007 : Change date range in pivot table chart
    By Tino XXL in forum Excel General
    Replies: 1
    Last Post: 02-10-2011, 12:25 PM
  2. macro for date change in pivot table
    By oframa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-23-2010, 04:54 AM
  3. Change text colour when entry made in another cell
    By thalt in forum Excel General
    Replies: 5
    Last Post: 01-10-2010, 07:13 AM
  4. Pivot table for made and sent dates
    By gozillasnack in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2007, 06:34 PM
  5. [SOLVED] Specific Date Query with Pivot Table
    By Diggsy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-15-2005, 12:16 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