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?

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?

• javascript
• datetime
• google-apps-script
• google-sheets

2 Answers 2

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');

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