+ Reply to Thread
Results 1 to 13 of 13

Highlight the dates from given month and past years

  1. #1
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Highlight the dates from given month and past years

    Greeting too all...


    i am trying to apply Conditional formatting on the dates of joining from given month and year in "S6 & X6" , Which i am not successful to do so again

    the 1st part is working fine to Highlight the dates which starts from 1st to 14th of every current month date and from 15th to 31th of previous month datw, with help of (Glenn Kennedy whom i am very grateful)

    BUT i need minor updation..

    i want to Highlight the month date from the given Month from given cell and previous years (i.e 2014, 2013,2012,2010.....). cant not figure out the solution

    any help will be highly appreciated....

    plz find the attachment

    joining Date Hihghlight 2.xlsx

    High Regards

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Highlight the dates from given month and past years

    select D:D
    and apply a conditional formatting formula and use

    =MONTH(D1)=MONTH(DATEVALUE("1"&$S$6&"2015"))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Highlight the dates from given month and past years

    Not sure what you want, but it might helps to build your own formula:

    With S6:month (mmmm), X6:year

    This returns day 14th of same month of previous year: =DATEVALUE("14/"&$S$6&"/"&$X$6-1)
    day 15th of previous month, previous year: =EDATE(DATEVALUE("14/"&$S$6&"/"&$X$6-1),-1)+1
    Quang PT

  4. #4
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: Highlight the dates from given month and past years

    Thanks for your Helpful reply appreciated. but unfortunately it don't solve my problem .. might be i am unable to explain my problem clearly..

    i am trying to apply formula on "Conditional formatting" on the dates of joining to highlight the those date which has completed 1 year cycle for Salary increment. And our company follow some rules for salary increment.

    Our Salary increment dates which starts from (1st to 14th) of its given month and (15th to 31th) its previous month, i.e If we are playing salary of the October month then those employees salary increment will be added who were appointed on "1st to 14th" October and "15th to 31th" its previous month (September) and this same will continue with the next coming month.

    The problem i am facing is with the Conditional formatting formula which i m applying to picks the month and year from my given cell in sheet NOT from my system (computer)

    Please Login or Register  to view this content.
    i have tried a lot on my own but cant figure it out

    any help will be highly appreciated

    Best Regards

  5. #5
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: Highlight the dates from given month and past years


  6. #6
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: Highlight the dates from given month and past years

    [QUOTE=This returns day 14th of same month of previous year: =DATEVALUE("14/"&$S$6&"/"&$X$6-1)
    day 15th of previous month, previous year: =EDATE(DATEVALUE("14/"&$S$6&"/"&$X$6-1),-1)+1[/QUOTE]

    The above formula works with out putting it inside the "conditional formatting"... can you guide how to use it inside the Conditional formatting

    any help will be appreciated

    Regards

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Highlight the dates from given month and past years

    Apply this for CF of D12:

    Please Login or Register  to view this content.
    I tried to convert date in D12 to be same day, same month in year of 2015 (cell X6), then compare it with 15th Oct and 14th Nov of 2015

  8. #8
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: Highlight the dates from given month and past years

    Quote Originally Posted by bebo021999 View Post
    Apply this for CF of D12:

    Please Login or Register  to view this content.
    I tried to convert date in D12 to be same day, same month in year of 2015 (cell X6), then compare it with 15th Oct and 14th Nov of 2015
    Many Many thanks for your Reply and help. there is some issue in Month which need to change

    When i enter Month October: It shows result "1st to 14th" November and "15th to 31th" its previous month (October)

    I need to accomplish When i enter Month October: It showing "1st to 14th" October and "15th to 31th" its previous month (September)

    Regards

  9. #9
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: Highlight the dates from given month and past years

    any help and solution will be highly appreciated

  10. #10
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Highlight the dates from given month and past years

    Select Column D and use below formula in conditional format

    =TEXT(EOMONTH($D12,(DAY($D12)>14)+0),"MMMM")=$S$6
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Highlight the dates from given month and past years

    Formula for CF for D12
    Please Login or Register  to view this content.
    Copy to other cells.
    Instead of entering month name in C6 , enter date and format the cell as Custom-->mmmm.Pl see file.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 11-03-2015 at 06:17 AM.

  12. #12
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: Highlight the dates from given month and past years

    Quote Originally Posted by nflsales View Post
    Select Column D and use below formula in conditional format

    =TEXT(EOMONTH($D12,(DAY($D12)>14)+0),"MMMM")=$S$6
    Working great

    We will always be grateful for all that you have done to help us (nflsales). And also to all those who reply and tried to Solve highly appreciated

    Reputation has been Added and Thread is marked as Solved

    Best Regards

  13. #13
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Highlight the dates from given month and past years

    Thanks for your feedback and adding reputation

+ 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. [SOLVED] Highlight the current or past month dates | Confuse|
    By pipsmultan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-30-2015, 04:48 AM
  2. Determine if a month falls between two dates - YEARS OVERLAPPING
    By eekbubble in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-18-2015, 12:03 PM
  3. Replies: 4
    Last Post: 05-15-2014, 04:39 PM
  4. [SOLVED] Highlight dates that are 3 years old as of the current date.
    By musik7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-10-2013, 12:31 PM
  5. Need a Macro to identify & then copy all rows containing dates within the past month.
    By Cliffster in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-08-2013, 06:00 AM
  6. recognizing dates past End of Month
    By sk81222 in forum Excel General
    Replies: 6
    Last Post: 06-14-2006, 10:43 AM
  7. Dates For Past Years
    By scw1217 in forum Excel General
    Replies: 0
    Last Post: 02-15-2005, 08:00 AM

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