Inputting Date without time

Question

Using the code below, the google sheet enters the Date in column 6 when a cell in column 1 is edited. It also includes the timestamp.

How can this code be altered so that just the date (day-month-year) is entered? This would allow searches by date on the sheet.

  function onEdit(e) {
    var sheetName = 'Cases Allocation'; //name of the sheet the script should work on 
    var colToWatch = 1
    var colToStamp = 6 //timestamp in col A
    if (e.range.columnStart !== 1 || e.source.getActiveSheet()
        .getName() !== sheetName || e.value == '') return;
    var writeVal = e.value !== "" ? new Date(),: '';
    e.source.getActiveSheet()
        .getRange(e.range.rowStart, colToStamp)
        .setValue(writeVal);
}

Answer 1

You can try using Utilities.formatDate. This method formats date according to specification described in Java SE SimpleDateFormat class. You can visit the date and time patterns here. An example of usage is:

// This formats the date as Greenwich Mean Time in the format
// year-month-dateThour-minute-second.
var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
Logger.log(formattedDate);

Do take note that using Utilities.formatDate has the following effects as noted in this post:

1. Utilities.formatDate does not change the cell format; it converts its content to text. If you are going to use the date value in computations, you don't want it to be converted to text.
2. Utilities.formatDate requires one to specify a timezone. If you put something like "GMT+1" and your country observes Daylight Saving Time, you can expect hard-to-track bugs to come up a few months later. With setNumberFormat, the date value remains consistent with all other date values in the spreadsheet, governed by its timezone setting.

To avoid these, you can also use setNumberFormat(numberFormat) to set the date or number format. The accepted format patterns are described in the Sheets API documentation. And an example usage is:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B2");
// Always show 3 decimal points
cell.setNumberFormat("0.000");

Answer 2

Thanks all - tried format etc, but then just decided to add a column and split the date and time, with just the date going into a new column.

Answer 3

function onEdit(e) {
    var sheetName = 'Cases Allocation'; //name of the sheet the script should work on 
    var colToWatch = 1
    var colToStamp = 6 //timestamp in col A
    if (e.range.columnStart !== 1 || e.source.getActiveSheet()
        .getName() !== sheetName || e.value == '') return;
    //var writeVal = e.value !== "" ? new Date(),: '';
    e.source.getActiveSheet()
        .getRange(e.range.rowStart, colToStamp)
        .setValue(new Date(new Date().setHours(0,0,0,0))).setNumberFormat('MM/dd/yyyy');
}

The below code will save the date without time. You can change the format as required. The field remains a date which can be used as required.

setValue(new Date(new Date().setHours(0,0,0,0))).setNumberFormat('MM/dd/yyyy');