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 118926 times)

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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: 3080
  • Total likes: 232
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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: 10041
  • Total likes: 3795
  • DansDeals.com Hat Tips 7
    • 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 »
The thing with the hamster is that running on the wheel is actually what it is trying to do.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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: 10041
  • Total likes: 3795
  • DansDeals.com Hat Tips 7
    • 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)



The thing with the hamster is that running on the wheel is actually what it is trying to do.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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: 3080
  • Total likes: 232
  • 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)

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11036
  • Total likes: 1118
  • 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: 3080
  • Total likes: 232
  • 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.