Home For everyone Chat and off-topic discussion
Current updates regarding coronavirus (Covid-19) and the precautions AAT are taking will be continually updated on the below page.

Please check this link for the latest updates:
We hope you are all safe and well and if you need us we will be here. 💚


What's wrong with this formula?

Gem7321Gem7321 Experienced MentorDevonMAAT, AAT Licensed Accountant Posts: 1,438
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

  • PGMPGM Font Of All Knowledge Registered Posts: 1,954
    try this;

    =IF(TODAY()-G19>90,"chase", IF(TODAY()-G19>30, "mark", IF(TODAY()-G19>14, "check", " ")))
  • Gem7321Gem7321 Experienced Mentor DevonMAAT, AAT Licensed Accountant Posts: 1,438
    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", " ")))
  • jamieleeukjamieleeuk New Member Registered Posts: 13
    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
  • PGMPGM Font Of All Knowledge Registered Posts: 1,954
    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
  • Gem7321Gem7321 Experienced Mentor DevonMAAT, AAT Licensed Accountant Posts: 1,438
    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.
  • PGMPGM Font Of All Knowledge Registered Posts: 1,954
    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?
  • Gem7321Gem7321 Experienced Mentor DevonMAAT, AAT Licensed Accountant Posts: 1,438
    most of them are future dates but some past, the formula still works and the cell goes red for the past dates
  • PGMPGM Font Of All Knowledge Registered Posts: 1,954
    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
  • jamieleeukjamieleeuk New Member Registered Posts: 13
    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.
Sign In or Register to comment.