+ Reply to Thread
Results 1 to 8 of 8

Highlighting date in last 3 months

  1. #1
    Registered User
    Join Date
    04-06-2017
    Location
    Wales
    MS-Off Ver
    EXCEL professional plus 2013
    Posts
    4

    Red face Highlighting date in last 3 months

    Hello,

    I would like to calculate quarterly or every 3 months the number of referrals I have had to a service. I have a column in which I enter the date the person entered the service. I would like to formulate the date cells to a different colour if they occurred in the last 3 months. Or if it is possible to create a total for the number of referrals occurring in the last quarter.

    I can see you can do conditional cell formatting for dates occurring in the last month but I don't know how to make it calculate 3 months.

    I'm enjoying trying to use excel to make my quarterly stats calculations easier and have managed to do most things, but this is frustrating me!

    Any help would be appreciated

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Highlighting date in last 3 months

    When you say "last quarter" do you mean "previous quarter"? So today it's April; you want to see data from January-March? Or do want current quarter-to-date?

    I would suggest a pivot table for that.

    Here is the rule for CF for previous quarter dates, using A1 as an anchor. Your anchor will be the top cell in the range of dates to apply this as a custom formula:

    =AND(DATE(YEAR(TODAY()),MONTH(TODAY())-2-MOD(MONTH(TODAY())+2,3)+1,1) <= A1, A1 <= DATE(YEAR(TODAY()),MONTH(TODAY())+1-MOD(MONTH(TODAY())+2,3)+1,1)-1)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-06-2017
    Location
    Wales
    MS-Off Ver
    EXCEL professional plus 2013
    Posts
    4

    Re: Highlighting date in last 3 months

    yes sorry, I meant the previous quarter. So it would be Jan- March as you say. Would I add this using the conditional formula option or just put in directly on to the spreadsheet? If it's the former, would I have to do a minimum and maximum value as it gives options for that?
    I'll try this onmonday and let you know how it goes. Thank you very much

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Highlighting date in last 3 months

    You would use that formula by highlighting the date cells, and creating a conditional formatting rule, and using the option "Use a formula to determine...". Replace A1 with the start of the actual range you will use. For example if your dates are in C2:C100, then change A1 to C2.

    "create a total for the number of referrals occurring in the last quarter." You could use a formula like this in a COUNTIFS formula, or you could create a pivot table. I can give specifics if you attach your file.

  5. #5
    Registered User
    Join Date
    04-06-2017
    Location
    Wales
    MS-Off Ver
    EXCEL professional plus 2013
    Posts
    4

    Re: Highlighting date in last 3 months

    Hi I tried the formula you gave it highlighted the dates Feb-April rather than Jan-March. I think I have attached the file . I hope its worked
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Highlighting date in last 3 months

    Hi All,

    in the attachment I'm using as formatting rule


    =AND(C2>=EOMONTH(TODAY(),-4)+1,C2<=EOMONTH(TODAY(),-1))


    Regards
    Attached Files Attached Files
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  7. #7
    Registered User
    Join Date
    04-06-2017
    Location
    Wales
    MS-Off Ver
    EXCEL professional plus 2013
    Posts
    4

    Re: Highlighting date in last 3 months

    Thank you! looks like it's working now, I'll keep an eye on it over the next few months as I add the data but this is going to make my life a lot easier


  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Highlighting date in last 3 months

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 11-01-2014, 10:45 PM
  2. [SOLVED] Creating a view by filtering 2 columns by date (both within 3 months of today's date)
    By Gti182 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-21-2014, 05:04 AM
  3. [SOLVED] Add date + 3 months in vba so the VBA sends mail reminder exactly 3 months before due date
    By JimmyQ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2014, 08:54 AM
  4. Dynamically Filling in Allocation in Months based only on Start Date and End Date
    By Computermoss in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-07-2013, 01:16 PM
  5. [SOLVED] Display cells 12 months prior and 12 months post from a given date
    By hog77 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-20-2012, 06:25 AM
  6. Replies: 15
    Last Post: 06-26-2012, 07:28 AM
  7. Replies: 2
    Last Post: 01-04-2012, 09:15 AM

Tags for this Thread

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