+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting "Text" Month from =TODAY() or =NOW()

  1. #1
    Registered User
    Join Date
    02-27-2009
    Location
    Worthing, England
    MS-Off Ver
    Excel 2000
    Posts
    3

    Conditional Formatting "Text" Month from =TODAY() or =NOW()

    Hi guys,

    Hoping you can help out. I'm currently setting up a spreadsheet for our Spot Checks, Supervision, etc. Whilst I see myself as a quite well versed Excel user this has me stumped.

    I have a cell which is providing the month at the moment, the cell is formatted to show "mmmm" with =TODAY().

    I then have a list of my staff on the left with columns adjacent to these with the month they are due to have Supervision, this has been simply entered as Text due to the fact that the appointment can be made any point during that month.
    I then want to Conditionally Format these cells.
    Equal To Month would bring the cell up RED
    Not equal to Month would bring the cell up GREEN.

    However, I have a feeling as the month is in TEXT then it is not reading the Date in the =TODAY cell as matching.

    I realise I could enter the months as a date then format it similarly to simply show the Month. However, I want to be using this spreadsheet into next year and obviously if I entered the dates of this year then it would not be valid into next year (Especially if I'm using a =NOW or =TODAY cell to show the month).

    Any help would be greatly appreciated.
    Many thanks in advance,
    Solomon

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Conditional Formatting "Text" Month from =TODAY() or =NOW()

    Solomon you don't specify how your text months have been entered, however, you should find that:

    MONTH(1&B1)
    where B1 = Month in TEXT etc...

    returns the month numerically and can then be compared to say

    MONTH($Z$1)
    Where Z1 holds =TODAY()

  3. #3
    Forum Contributor
    Join Date
    10-11-2007
    Location
    Sweden
    MS-Off Ver
    365
    Posts
    251

    Re: Conditional Formatting "Text" Month from =TODAY() or =NOW()

    I have a cell which is providing the month at the moment, the cell is formatted to show "mmmm" with =TODAY().
    Instead of formatting that cell, try to use =Text(Today(),"mmmm").

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Conditional Formatting "Text" Month from =TODAY() or =NOW()

    Obviously using Steve R's approach it is imperative that the format of the TODAY string is identical to that used in your column containing the months as text... ie if values are "Jan", "Feb" you must format TODAY as "mmm" ...

    The coercion of text to number will cater for all abbreviations in your column containing months as text... ie if Jan sometimes entered as January etc the 1&text will still coerce to the correct month.

    Performance wise there's probably little in it -- normally comparison of text versus comparison of number would be slower but in this instance you're coercing lots of text to number before comparing so little in it... I guess the 1&text method is less prone to error (arguably).

  5. #5
    Registered User
    Join Date
    02-27-2009
    Location
    Worthing, England
    MS-Off Ver
    Excel 2000
    Posts
    3

    Re: Conditional Formatting "Text" Month from =TODAY() or =NOW()

    Hi guys,

    This is a relatively small spreadsheet (Only aroudn 25 workers) so I've opted for the easier option and simply gone with the =Text(Today(),"mmmm"). Option.

    Many many thanks all, you have been brilliant.
    Everydays a school day =]

  6. #6
    Registered User
    Join Date
    02-27-2009
    Location
    Worthing, England
    MS-Off Ver
    Excel 2000
    Posts
    3

    Re: Conditional Formatting "Text" Month from =TODAY() or =NOW()

    EDIT: Resolved. Thanks.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Conditional Formatting "Text" Month from =TODAY() or =NOW()

    That's great but please mark as such: http://www.excelforum.com/faq.php

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1