How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional formatting in Excel 2013/2016.

Highlight Cell if Date is in Current Day/Week/Month


To highlight the cell of current day or the date is in the current week or month, you can do the following steps:

#1 select the range of cells that you want to highlight date.

highlight cell or row if date 1

#2 go to HOME tab, click Conditional Formatting command under Styles group, and click Highlight Cells Rules menu from the drop down menu list, then select A Date Occurring sub menu. And the A Date Occurring dialog will appear.

highlight cell or row if date 2

#3 if you want to highlight the current day, then select Today option from the first drop down list box in the A Date Occurring dialog box, then choose one color that you want to fill the cell in the second drop down list box. Click Ok button.

highlight cell or row if date 3

Note: if you want to highlight the cell if date is in the current week, just choose This Week option from the first drop down list box. And if you want to highlight the cell if date is in the current month, just choose This Month from the first drop down list box.

#4 you should notice that the current date has been highlighted.

highlight cell or row if date 4

Highlight Row if Date is in Current Day/Week/Month


If you want to highlight the row that If the date in that row is equal to the current day or is in the current week or month, you can do the following steps:

#1 select the entire rows of data that contain date values.

highlight cell or row if date 1

#2 go to HOME tab, click Conditional Formatting command under Styles group, Click New Rule from the drop down menu list. And the New Formatting Rule dialog will open.

highlight cell or row if date5

#3 click Use a formula to determine which cells to format option in the New Formatting Rule dialog box, type the following formula in the Format values where this formulas is true text box.

highlight cell or row if date6

To highlight current date, use the following formula:

=$A2=TODAY()

To highlight row if date is in the current week, use the following formula:

=TODAY()-WEEKDAY(TODAY(), 3)=$A2-WEEKDAY($A2, 3)

To highlight row if date is in the current month, use the following formula:

=TEXT($A2,”mmyy”)=TEXT(TODAY(),”mmyy”)

Note: the Cell A2 is the first cell of your selected range.

#4 click Format button, and the Format Cells dialog will open. Click Fill tab, select on color that you want to use. Click Ok button.

highlight cell or row if date7

#5 click Ok button. You should see that the entire rows which contain the current day have been highlighted.

highlight cell or row if date8

Related Functions


  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

 

 

Related Posts

How to Count Dates by Day of Week in Excel
count dates by days of week7

This post will guide you how to count days of week in the range of dates using a formula in Excel 2013/2016 or Excel office 365. How do I count dates by day of week in Excel. Count Dates by ...

How to Sum Data by Weekday with Different Formulas/Functions in Excel
Sum Data by Weekday 20

To generate a business summary report for further investigation, we can sum income or profits for a date like Monday, Tuesday …etc. to check on which date we have the top income in a week, then we can estimate the ...

How to Calculate Number of Weekends between Two Dates in Excel
calculate number of weekdays3

This post will guide you how to count the number of weekends between two given dates in Excel. How to find out how many weekends are between two dates in Excel. How to count the number of Sundays between two ...

Filter Out Weekends and Weekday
filter out weekday and weekend4

This post will guide you how to filter out weekend or weekday from a list of data in Excel. How do I filter out the data in all Weekends in Excel. How to filter out all data in all weekdays ...

Determine If a Date Falls on the Weekend
determine date falls weekend1

This post will guide you how to determine if a date falls on the weekend in Excel. How do I check if a date falls on the weekend with a formula in Excel. How to check if a date is ...

Count the Number of Weekends between Two Date
count number of weekend between two dates2

This post will guide you how to count the number of weekends between two dates in Excel. How do I get the number of weekends between 2 dates with a formula in Excel. How to count weekend days between dates ...

Converting Week Number to Date
convert week to date4

This post will guide you how to convert a week number to a date with a formula in Excel. How do I calculate a date from a week number and a year with formula in Excel. Or how to get ...

Combining Date and Time into One Cell
combining date and time2

This post will guide you how to combine a data column and time column into one column in Excel. How do I quickly combine data and time in the different cells into one cell in your Worksheet. For example, if ...

Convert Month Name to Number
convert month name to number6

This post will guide you how to convert month name to number in excel. How do I convert month name to number with an excel formula. How to convert month name to number with an Excel VBA macro. How to ...

Get the First Monday of a Given Year
get first monday of the year2

This post will guide you how to get the first Monday of a given year or date in excel. How do I calculate the first Monday of a specific year in excel. How to determine the first Monday in a ...

Sidebar