Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 158805 times)

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1060 on: October 30, 2017, 05:30:45 PM »
I am trying to format a column listing due dates to highlight upcoming dates using conditional formatting 3 color scale but only want it to highlight dates which are within 6 months from now. I am having a difficult time limiting it to within 6 months. I am guessing that I would enter it into the maximum field under conditional formatting but can't get it right.
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1061 on: October 30, 2017, 05:48:26 PM »
I am trying to format a column listing due dates to highlight upcoming dates using conditional formatting 3 color scale but only want it to highlight dates which are within 6 months from now. I am having a difficult time limiting it to within 6 months. I am guessing that I would enter it into the maximum field under conditional formatting but can't get it right.
You'll need to use a formula rule.

EX: =A1<DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1062 on: October 30, 2017, 05:56:24 PM »
You'll need to use a formula rule.

EX: =A1<DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

I am trying to use scaled colors like this


But it does not allow relative references in the formulas which I was trying to put into maximum.
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1063 on: October 30, 2017, 05:59:31 PM »
I am trying to use scaled colors like this


But it does not allow relative references in the formulas which I was trying to put into maximum.
Hmm... it looks like with color scales you can only input a starting number/formula and a percentile to switch to the middle color.

What do you want dates after six months from now to look like? No formatting?
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1064 on: October 30, 2017, 06:01:21 PM »
Hmm... it looks like with color scales you can only input a starting number/formula and a percentile to switch to the middle color.

What do you want dates after six months from now to look like? No formatting?
Correct no formatting. I am not sure why a maximum of =today()+183 doesn't work.
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1065 on: October 30, 2017, 06:08:25 PM »
Correct no formatting. I am not sure why a maximum of =today()+183 doesn't work.
Hmmm... you can add a second rule of "no formatting" if the value is more than six months away, but then you lose that max color

Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1066 on: October 30, 2017, 06:12:33 PM »
What about adding a hidden column that shows the date, but only if it is within six months? Then you can reference that hidden column in your conditional formatting
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1067 on: October 30, 2017, 06:15:34 PM »
Formula for hidden column:

=IF(A1<DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY())),A1,"")

Let's say that column is column G, then replace A1 with G1 in your conditional formatting formula.

Then just hide column G.

Let me know if you need more details
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1068 on: October 30, 2017, 06:17:30 PM »
Hmmmm it looks like you can't use a color scale rule on data from another column :(
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1069 on: October 30, 2017, 06:20:07 PM »
Give me a minute, I have an idea....
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1070 on: October 30, 2017, 07:16:39 PM »
Just want to point out that color scale is not really a great solution here. Color scale is always relative to the other values formatted under the rule. If you want it to be colored based on how close the due date is, I would think you'd want relative to today, not relative to the other dates in the data set.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1071 on: October 30, 2017, 07:17:54 PM »
Got it! No hidden columns needed. You'll need 2 rules:



The first has this formula:
Code: [Select]
=A1>DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))
For formatting, go to the Fill tab and click "no color".

Make sure Stop If True is checked.

The second rule should be the color scale rule:



Minimum: Lowest value

Midpoint: formula
Code: [Select]
=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))aka 3 months from today

Maximum: formula
Code: [Select]
=DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))aka 6 months from today

Result:



Enjoy!
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1072 on: October 30, 2017, 07:19:30 PM »
Just want to point out that color scale is not really a great solution here. Color scale is always relative to the other values formatted under the rule. If you want it to be colored based on how close the due date is, I would think you'd want relative to today, not relative to the other dates in the data set.
To achieve this, for your minimum use this formula:

Code: [Select]
=today()
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1073 on: October 30, 2017, 07:26:44 PM »
I figured part out. The formulafor the minimum and maximum dates need to be entered with "number" selected. That makes it that anything below the minimum is formatted as the minimum and anything above the max is formatted as the max. Now I should be able to use a second rule of no formatting but I can't seem to get that to work. What formula did you use?

ETA: I wrote this before I saw this reply. The place I was going wrong was that I wasn't checking stop if true.
Got it! No hidden columns needed. You'll need 2 rules:



The first has this formula:
Code: [Select]
=A1>DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))
For formatting, go to the Fill tab and click "no color".

Make sure Stop If True is checked.

The second rule should be the color scale rule:



Minimum: Lowest value

Midpoint: formula
Code: [Select]
=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY()))aka 3 months from today

Maximum: formula
Code: [Select]
=DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))aka 6 months from today

Result:



Enjoy!
« Last Edit: October 30, 2017, 07:32:08 PM by aygart »
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1074 on: October 30, 2017, 07:29:20 PM »
I figured part out. The formulafor the minimum and maximum dates need to be entered with "number" selected. That makes it that anything below the minimum is formatted as the minimum and anything above the max is formatted as the max. Now I should be able to use a second rule of no formatting but I can't seem to get that to work. What formula did you use?
=A1>DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

If you're using a different column, change "A1" accordingly. Also note as per above, you need to manually choose "no color" in the Fill tab of the formatting box.
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12649
  • Total likes: 7333
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1075 on: October 30, 2017, 07:36:44 PM »
Thanks for your help!

One thing I realized was that the minimum value is important as well because without it if there are a few overdue items than some will have scaled down color even though they are overdue. With a minimum of today they will all be max color (I did today+35)



Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1076 on: October 30, 2017, 07:37:41 PM »
Thanks for your help!

One thing I realized was that the minimum value is important as well because without it if there are a few overdue items than some will have scaled down color even though they are overdue. With a minimum of today they will all be max color (I did today+35)


Right! You're welcome!
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1077 on: October 30, 2017, 08:33:02 PM »
=A1>DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

If you're using a different column, change "A1" accordingly. Also note as per above, you need to manually choose "no color" in the Fill tab of the formatting box.
I do a lot of work with dates and have gotten the ins and outs of all the different date related functions. You can collapse the above to:
=A1>EDATE(TODAY(),6)

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12104
  • Total likes: 2156
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1078 on: October 30, 2017, 08:34:11 PM »
I do a lot of work with dates and have gotten the ins and outs of all the different date related functions. You can collapse the above to:
=A1>EDATE(TODAY(),6)
oooh, nice! thanks!

is there a corresponding function for days/years?
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3307
  • Total likes: 517
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1079 on: October 30, 2017, 08:43:05 PM »
oooh, nice! thanks!

is there a corresponding function for days/years?
Days works without a function, and years you'd get by just multiplying the number by 12 and entering it as that number of months.