How To Enable Gaming Mode on Android 12

Back in Februry, folks at XDA had spotted an unreleased Gaming Dashboard in the works for Android 12. But up until now, there were no clear...

Tuesday

How to Highlight Recurring Event or Payment Dates in Google Sheets

Posted by   on

If you want to highlight a recurring event or payment dates in Google Sheets, you can use a custom format rule in Format > Conditional Formatting.


How do we use that?


Let me dig deep into it.


We can highlight a cell or cell range in several ways in Google Sheets.


Here are some of the most common practices.



  1. Draw a thick border around the cells.

  2. Increase the font size.

  3. Use a background (fill) color.

  4. Make the text Bold, Italic, Underline, Strikethrough.

  5. Change the Text Color.


To highlight a recurring event or payment dates in Google Sheets, we can choose single or multiple highlighting options mentioned in points 3, 4, and 5 above.


They are the rules presently supported in Conditional Formatting.


Recurring events/payments can be weekly/biweekly/monthly meetings, monthly or periodic bill payments, subscriptions, gym memberships, etc.


Assume we have a recurring event that takes place every other week. Let’s see how to write a highlighting rule for that event.


We can later modify that formatting rule suitable for the predefined recurring event/payment interval.


Example to Highlighting Recurring Event or Payment Dates in Google Sheets


Here are two examples that to use depending on the orientation of the data that you have.


The Rule for Vertically Arranged Data


You can use the below recurring event/payment highlight rule, based on OR and MOD when the event/payment dates are arranged vertically in B2:B.


=or(B2=$F$1,mod(B2-$F$1,14)=0)

Assuming cell F1 contains the first event/payment date.


Rule to Highlight Recurring Event Dates in Google Sheets

The above format rule highlights dates in column B biweekly basis starting from the event/payment start date in F1, i.e., 28/07/2021.


To apply the above rule, please follow the below quick steps.



  1. Select B2:B100 (or the cell range that you want in column B).

  2. Click Format > Conditional Formatting.

  3. Apply to Range > B2:B100.

  4. Format Rules > Custom formual is.

  5. Insert the rule.

  6. Formatting Style > Fill Color > Green or your choice of color.


Points to be Noted


In the above example, the dates are arranged in chronological order. It’s not necessary to follow that. Even if the dates are shuffled, the formula (format rule) will work.


The dates sequence is also not necessary.


The above rule highlights the recurring event/payment dates happening every other week (biweekly).


To change the frequency of the events, change 14 in the formula with the corresponding days between two events/payments.


I have multiple events/payments. How do I apply all of them?


Example:-


If you have one more event, enter that event start date in cell F2.


Here is the second rule to use. Don’t forget to choose a different color.


=or(B2=$F$2,mod(B2-$F$2,14)=0)

This way you can apply more format rules.


Highlight Horizontally Arranged Recurring Event or Payment Dates


This time I want to highlight the recurring event dates in B2:Z2.


The event start date is in cell B5.


=or(B2=$B$5,mod(B2-$B$5,14)=0)

Horizontal Dates and Highlight Rule

To apply the rule, please follow the above six quick steps (the cell range should be B2:Z2, not B2:B100).


Resources


Resources Related to Highlighting Duplicates in Google Sheets

No comments:
Write Comments

Hello Friends, welcome to autobloginc.blogspot.com we Hope You'll like it - COntact US
!!THANK YOU FOR YOUR SUPPORT!!