How to get day of the week in Google Sheet

At times we want to show day of the week along with the date on the google sheet. The day gives us a fair idea of what to expect when planning an event like birthday party or a conference.

Attendance at parties and conferences can vary based on day of the week. In towns and cities traffic is high or low on certain days. Libraries and government office are open for long hours on certain days. Doctors and dentist keep extended hours on particular day. Knowing the day can be helpful when organizing an event or requesting appointments

If you use Google Sheets to track events and usually refer to a calendar to get the day, this script will come handy. Instead to using a calendar you can use the script shown here to automatically get the day when you enter a date.

Add this simple Google script to the Google Sheet. The blog also shows how to account for different date formats. dd/mm/yyyy and mm/dd/yyyy.

Create new Google Sheet or open an existing one.

Change date format based on Location

Widely used date formats across the world are mm/dd/yyyy or dd/mm/yyyy. To make sure that the script works as expected change the setting in Google Sheet to reflect the date format used in the Sheet. On the Google Sheet, goto File -> Spreadsheet settings and change the Locale

From Tools Menu select Script Editor

OnEdit(e) Trigger

In the script editor clear the myFunction() and replace with onEdit(e) function. onEdit is Google script action that occurs every time a cell is edited.

Modify the script accordingly based on where the date is and where the day should appear. Change line 9 in the script to match the date column and Change line 10 and line 17 to match the column where day will show. Use (+)add or (-)subtract on these lines to get the cell location. Save the function. Give project name, if prompted.

function onEdit(e) {
   // Set a comment on the edited cell to indicate when it was changed.
 var mpd = 1000 * 60 * 60 * 24;
   var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone()
   var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
   var col = cell.getColumn()
   var row = cell.getRow()
   var arr = ['Sunday', 'Monday','Tuesday', 'Wednesday','Thursday', 'Friday' ,'Saturday']
   if (col == 2) {
     SpreadsheetApp.getActiveSheet().getRange(row, col+2).setValue(null)
     if (SpreadsheetApp.getActiveSheet().getRange(row, col).getValue() == '' ){
         return
         }
     dt = SpreadsheetApp.getActiveSheet().getRange(row, col).getValue().getTime()
     var val = new Date(dt+mpd)
     var n = val.getDay()
     SpreadsheetApp.getActiveSheet().getRange(row, col+2).setValue(arr[n])
   }
 }