What's wrong with this formula?

Gem7321
Gem7321 Registered Posts: 1,438 Beyond epic contributor πŸ§™β€β™‚οΈ
I need an excel whizz please!

I have a date in cell G19, in another cell I want it to turn green if the date in G19 is less than 90 days from today, to turn amber if it's less than 30 days from today, and to turn red if it's less than 14 days from today. If it's more than 90 days it can just stay white.

I've used the following nested if statement:

=IF(G19-TODAY()<90,"chase", IF(G19-TODAY()<30, "mark", IF(G19-TODAY()<14, "check", " ")))

And conditional formatting to turn cells green if they say 'chase', to turn them amber if they say 'mark' and to turn red if they say 'check'.

I didn't use conditional formatting on its own as we want to be able to type over the cell with the formula for it to remove all conditional formatting.

But, regardless of the date my cell stays green unless it's more than 90 days then it stays white.

Help!

Comments

  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor πŸ§™β€β™‚οΈ
    try this;

    =IF(TODAY()-G19>90,"chase", IF(TODAY()-G19>30, "mark", IF(TODAY()-G19>14, "check", " ")))
  • Gem7321
    Gem7321 Registered Posts: 1,438 Beyond epic contributor πŸ§™β€β™‚οΈ
    Thanks PGM sorted now - my formula was back to front (thanks Beccalou( so now this is working

    =IF(G19-TODAY()<14,"chase", IF(G19-TODAY()<30, "mark", IF(G19-TODAY()<90, "check", " ")))
  • jamieleeuk
    jamieleeuk Registered Posts: 13 New contributor 🐸
    You could use VBA to automatically change the colour of a cell based on the word entered into that cell -

    Go into the spreadsheet and the particular sheet where the cell colour change is to happen, either press Alt+F11 or right-click on the tab name and choose "View Code".

    At the top of the screen will be two drop down boxes, choose the left one and select "Worksheet" and now from the right hand side on choose "Change"

    Put your cursor between the lines that say :

    Private Sub Worksheet_Change....

    End Sub

    Copy this code and paste it in between the 2 lines above.

    NB - you will need to change the range to suit your needs - IE, if you knew that the words CHECK, MARK and CHASE are only going to appear in one column, use that column otherwise you can choose A1: F100.

    In the second example, if there was the word "CHECK" in cell E45, it would turn red.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Range("E2:E500")
    If cell.Value = "Check" Then
    cell.Interior.Color = XlRgbColor.rgbLightGreen
    ElseIf LCase(cell.Value) = "check" Then
    cell.Interior.Color = XlRgbColor.rgbLightGreen
    ElseIf cell.Value = "Mark" Then
    cell.Interior.Color = XlRgbColor.rgbOrange
    ElseIf LCase(cell.Value) = "mark" Then
    cell.Interior.Color = XlRgbColor.rgbOrange
    ElseIf cell.Value = "Chase" Then
    cell.Interior.Color = XlRgbColor.rgbRed
    ElseIf LCase(cell.Value) = "chase" Then
    cell.Interior.Color = XlRgbColor.rgbRed
    End If
    Next cell
    End Sub

    All the best
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor πŸ§™β€β™‚οΈ
    Gem7321 wrote: Β»
    Thanks PGM sorted now - my formula was back to front (thanks Beccalou( so now this is working

    =IF(G19-TODAY()<14,"chase", IF(G19-TODAY()<30, "mark", IF(G19-TODAY()<90, "check", " ")))

    this formula doesn't work for me, it can't do
  • Gem7321
    Gem7321 Registered Posts: 1,438 Beyond epic contributor πŸ§™β€β™‚οΈ
    Sorry Jamie that sounds far too complicated for me! I think I'll stick with conditional formatting :D

    PGM it's working fine for me, just now that the trigger words are the other way around so chase is red, mark is still amber and check is green.
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor πŸ§™β€β™‚οΈ
    Gem7321 wrote: Β»

    PGM it's working fine for me, just now that the trigger words are the other way around so chase is red, mark is still amber and check is green.

    Is the date in G19 past or future?
  • Gem7321
    Gem7321 Registered Posts: 1,438 Beyond epic contributor πŸ§™β€β™‚οΈ
    most of them are future dates but some past, the formula still works and the cell goes red for the past dates
  • PGM
    PGM Registered Posts: 1,954 Beyond epic contributor πŸ§™β€β™‚οΈ
    Gem7321 wrote: Β»
    most of them are future dates but some past, the formula still works and the cell goes red for the past dates

    Ah right, I'd assumed it was for chasing debtors, mine works for that :D
  • jamieleeuk
    jamieleeuk Registered Posts: 13 New contributor 🐸
    It's easier and more maintainable my way I would have thought. Flexibility with the range covered, no need to copy and drag down formulae. But, as my mother always said, if it aint broke, don't fix it.
Privacy Policy