How to highlight a cell containing the name of a weekday in a row based on =Today() in Google Sheets

Question

I have a sheet with the names of the weekdays in row 1. I want to highlight the cell containing the value "Fri" on a Friday. I used the example from this answer: How do I highlight cells based on the day of the week in Google Sheets?

However, I need a row highlighted, and the example given is a column. I managed to make a semi-working example by switching the VLOOKUP to HLOOKUP and adjusting the parameters.

=IF(TEXT(TODAY(),"ddd")=IFERROR(HLOOKUP({{"M","Mon"};{"T","Tue"};{"W","Wed"};{"TH","Thu"};{"F","Fri"};{"S","Sat"};{"SU","Sun"}},  B1:H1, 1)),1)

However, it highlights Tue through Fri on a Friday!

Any chance someone could help me out? Here's an example sheet: https://docs.google.com/spreadsheets/d/1nhyc9r-A3IFaxfBz-HsvOeVTUFSDD3auHJb33ckg_5o/edit?usp=sharing

Answer 1

use:

=TEXT(TODAY(),"ddd")=B1

Answer 2

An alternative to the Sheets formula already provided is to make use of Apps Script:

function colorDayOfTheWeek() {
  let ss = SpreadsheetApp.getActiveSheet();
  let date = new Date();
  let today = date.getDay();
  ss.getRange(1, 1, 1, 8).setBackground("white");
  ss.getRange(1, today+1).setBackground("yellow");
}

Using a script can be easier to manage and it is more versatile when it comes to the tasks you have to achieve. The above one makes use of the getDay method which returns the day of the week and based on the returned value, it colors the cell accordingly.

Before

After

Reference

• JavaScript Date.prototype.getDay();

• Apps Script.