Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove timestamp from date in Google Apps Script

I'm using this to snag a date value for cells that are created when a spreadsheet updates:

setValue(new Date()).setNumberFormat('MM/dd/yyyy')

But what I get back includes the timestamp (i.e. 10/31/2015 22:16:55), rather than just the date (10/31/2015).

This creates a problem with the logic I'm using that references that cell. The logic is a basic countif statements that references date only. I could adjust to deal with timestamp but I would rather just not have the timestamp. Seems basic but I couldn't find any solution. What am I missing?

like image 832
Dave Avatar asked Nov 01 '15 05:11

Dave


People also ask

How do I remove the Timestamp from a date in Google Sheets?

Method 1: Use SPLIT Function The SPLIT function can split the contents of a cell into multiple cells. Since the timestamp is composed of Date and Time, we can use the SPLIT function to extract the Date to one cell and Time to another cell.

How do I change the date format in Google Apps Script?

Use Utilities. formatDate(date, timeZone, format) to format a date object in this format.


2 Answers

The format won't change the fact that the date value is still containing the different times.

To truncate the time from the date value itself try the following:

setValue(new Date(new Date().setHours(0,0,0,0))).setNumberFormat('MM/dd/yyyy');
like image 89
Robin Gertenbach Avatar answered Nov 02 '22 23:11

Robin Gertenbach


You can try something like this:

var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");

example implementation would be something like:

// where sheet is a predefined sheet, and getRange is cell F1
sheet.getRange(1,6).setValue(formattedDate).setNumberFormat('MM/dd/yyyy')

For more details Refer here

like image 30
Pushkar Bhagwat Avatar answered Nov 03 '22 00:11

Pushkar Bhagwat