Greetings, esteemed members of the Excel community
I'm trying to come up with a conditional formatting formula to highlight the dates that belong in the next ODD NUMBER months (1, 3, 5, 7 etc) along the range regardless of what first date is.
Dates belong in the same month as the first date remain non-highlighted. Only the dates belong in the next 1, 3, 5 etc months will be highlighted.
I'm sure the solution is quite simple, but for some reason I'm drawing a complete blank.
I don't understand your Q. Please manually shade cells to show the desired result.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
CF rule for B4:
=ISODD(MONTH(B4))
Applies to: =$B$4:$B$35
AliGW on MS365 Beta Channel (Windows 11) 64 bit
DATES
Sheet: Sheet1
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
i'm not sure i follow exactly - if you just want to highlight months that are ODD
=ISODD(MONTH(B4))
but i dont think thats what you are after and you are not showing an expected results , are you able to put some expected results and why in the sample and on the text in the question
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.
One guess:
=ISODD(month(B4))
applied to B4:B40
@Glenn like Ali posted
@Ali The problem with that solution is, when I change the first date to July, then the first month is highlighted, which I do not want. If the first month is June; months July, September, etc are highlighted. But if the first month is July; months like August, October, etc are highlighted. That's the part that stumped me.
Try this:
=AND(MONTH(B4)<>MONTH(TODAY()),ISODD(MONTH(B4)))
Select the cells B4:B35, then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:
=ISODD(MONTH(B4))
Click on the Format button | Fill tab and choose your colour, then OK your way out.
Hope this helps.
Pete
No - try this instead:
=AND(MONTH(B4)<>MONTH($B$4),ISODD(MONTH(B4)))
Reattached the file. Sorry for confusion, I thought the wording was clear.
CF rule for cell B5:
=MOD(MONTH(B5),2)<>MOD(MONTH($B$4),2)
See post #9.
Further to Post #6, use this formula for the CF rule instead:
=MOD(MONTH(B4),2)<>MOD(MONTH($B$4),2)
Hope this helps.
Pete
That's completely different!
Try this:
=OR(AND(ISODD(MONTH($B$4)),ISEVEN(MONTH(B4)),AND(ISEVEN(MONTH($B$4)),ISODD(MONTH(B4)))
It really was not and you never mentioned even months needing to be highlighted. Moral of the tale: always provide a very clear worked example in a workbook!I thought the wording was clear.
Last edited by AliGW; 06-13-2024 at 11:25 AM.
Thank you all so much for quick responses!
Pete's solution is quite clean and works beautifully!Thank you again!
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Glad to help, and thanks for the rep.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks