Remove time from dd/mm/yyyy hh:mm:ss format but removing part of string?-CodePudding

I have a date and time in cell C4, in the following format: 01/01/2022 12:30:00 My main goal is to remove the time and just have the following: 01/01/2022

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var daily_data = spreadsheet.getSheetByName("Daily Data");

var dateandtime = daily_data.getRange("C4").getValue().toString();
var date = dateandtime.substring(0,10);
daily_data.getRange("C4").setValue(date);

However this just outputs the following: Fri Dec 31

CodePudding user response：

From your script, I thought that the value of "C4" might be the date object. If my understanding is correct, how about the following modification?

Modified script:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var daily_data = spreadsheet.getSheetByName("Daily Data");

daily_data.getRange("C4").setNumberFormat("dd/MM/yyyy");

or, if you want to set the time to 00:00:00, please test the following script.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var daily_data = spreadsheet.getSheetByName("Daily Data");

var dateObj = daily_data.getRange("C4").getValue();
dateObj.setHours(0);
dateObj.setMinutes(0);
dateObj.setSeconds(0);
daily_data.getRange("C4").setValue(dateObj).setNumberFormat("dd/MM/yyyy");

In this modification, the number format of the cell is changed to dd/MM/yyyy.
From your question, I thought that your expected format might be dd/MM/yyyy. If you want MM/dd/yyyy, please modify it.

Reference:

setNumberFormat(numberFormat)

CodePudding user response：

ok, first one question: why do you need the cell having date and time and why do you need to extract just the time? Having a date column and a time column it's not a possibility?

That said... You can use .getDisplayValue() to get what exactly you see in your spreadsheet.

var dateandtime = daily_data.getRange("C4")..getDisplayValue().toString();

The code would be:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var daily_data = spreadsheet.getSheetByName("Daily Data");

var dateandtime = daily_data.getRange("C4").getDisplayValue().toString();
var date = dateandtime.substring(0,10);
daily_data.getRange("C4").setValue(date);