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

Offline aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4817
  • Total likes: 452
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1080 on: October 30, 2017, 08:50:24 PM »
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.
How would you do days without a function?
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8581
  • Total likes: 137
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1081 on: October 30, 2017, 08:53:02 PM »
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.
Cool!
Workflowy. You won't know what you're missing until you try it.

Offline skyguy918

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2011
  • Posts: 2455
  • Total likes: 36
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1082 on: October 30, 2017, 10:57:26 PM »
How would you do days without a function?
EDATE takes a date, and moves forward or backward x number of months from a specific date (keeping the day of the month static). If you want to take a date and move forward or backward x number of days, just add or subtract that number.

If A1 contains 11/1/17, putting =A1+10 into another cell would give you 11/11/17.

Offline aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4817
  • Total likes: 452
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1083 on: October 30, 2017, 11:04:17 PM »
EDATE takes a date, and moves forward or backward x number of months from a specific date (keeping the day of the month static). If you want to take a date and move forward or backward x number of days, just add or subtract that number.

If A1 contains 11/1/17, putting =A1+10 into another cell would give you 11/11/17.
Oh so you meant that you still need the TODAY. That is what I did.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4817
  • Total likes: 452
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1084 on: November 07, 2017, 07:55:25 PM »
I recorded a macro to do all of these. Now I need to get rid of the extraneous parts, but it works if my personal.xlsb is open, but it seems to not always open with excel.

Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?
Text to columns, (option in data ribbon ) but don't select anything ( tab, space etc) as the deliminator, so it all stays in same column.
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.
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)





Here is the macro as it is now.

Code: [Select]
    Columns("G:G").Select
       Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
        Selection.NumberFormat = "m/d/yyyy"
       
       
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(1).Value = "=TODAY()+30"
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = "=TODAY()+95"
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(3).Value = "=TODAY()+190"
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 5296274
        .TintAndShade = 0
    End With
    Columns("G:G").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=G1>DATE(YEAR(TODAY()),MONTH(TODAY())+10,DAY(TODAY()))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6553
  • Total likes: 85
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1085 on: November 07, 2017, 08:02:56 PM »


I recorded a macro to do all of these. Now I need to get rid of the extraneous parts, but it works if my personal.xlsb is open, but it seems to not always open with excel.

Here is the macro as it is now.

Code: [Select]
    Columns("G:G").Select
       Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
        Selection.NumberFormat = "m/d/yyyy"
       
       
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(1).Value = "=TODAY()+30"
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = "=TODAY()+95"
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueNumber
    Selection.FormatConditions(1).ColorScaleCriteria(3).Value = "=TODAY()+190"
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 5296274
        .TintAndShade = 0
    End With
    Columns("G:G").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=G1>DATE(YEAR(TODAY()),MONTH(TODAY())+10,DAY(TODAY()))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .Pattern = xlNone
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
End Sub

It always should open in the background in Excel. It's not there when you select unhide?

Offline aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4817
  • Total likes: 452
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1086 on: November 07, 2017, 08:06:26 PM »

It always should open in the background in Excel. It's not there when you select unhide?
nope. Unhide is greyed out.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6553
  • Total likes: 85
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood

Offline aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4817
  • Total likes: 452
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4817
  • Total likes: 452
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1089 on: November 07, 2017, 08:34:43 PM »
In the end this is what worked
https://answers.microsoft.com/en-us/office/forum/office_2007-excel/1-personalxlsb-will-not-load-automatically-2-the/97dd3d4a-7176-e011-8dfc-68b599b31bf5

To (1):

- Remove anything from the XLSTART folder
- Open Excel
- Start recording a macro and set "Personal Macro Workbook" as destination.
- Select some cells (we must record something!)
- Stop recording (the file PERSONAL.XLSB is created)
- Open your old PERSONL.XLS
- Open the VBA editor
- Move the modules with Drag&Drop to the PERSONAL.XLSB file
- Save the PERSONAL.XLSB file.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6553
  • Total likes: 85
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1090 on: November 07, 2017, 10:26:00 PM »
In the end this is what worked
https://answers.microsoft.com/en-us/office/forum/office_2007-excel/1-personalxlsb-will-not-load-automatically-2-the/97dd3d4a-7176-e011-8dfc-68b599b31bf5

To (1):

- Remove anything from the XLSTART folder
- Open Excel
- Start recording a macro and set "Personal Macro Workbook" as destination.
- Select some cells (we must record something!)
- Stop recording (the file PERSONAL.XLSB is created)
- Open your old PERSONL.XLS
- Open the VBA editor
- Move the modules with Drag&Drop to the PERSONAL.XLSB file
- Save the PERSONAL.XLSB file.
This is what I meant btw with macros messing everything up and not being worth the time and effort most times.

I almost never had a good long lasting experience with my self recorded/edited macros saved in personal...

Offline aygart

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2008
  • Posts: 4817
  • Total likes: 452
  • DansDeals.com Hat Tips 5
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1091 on: November 07, 2017, 10:27:24 PM »
This is what I meant btw with macros messing everything up and not being worth the time and effort most times.

I almost never had a good long lasting experience with my self recorded/edited macros saved in personal...
I just saved a backup of this one right here in this thread.
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.