New answers tagged

0

The error indicates that the values are not datetimes but text strings that look like datetimes. Set File > Spreadsheet settings > Locale so that the date and time formats used by the spreadsheet match those of your data, then repaste the data. See this answer for an explanation of how date and time values work in spreadsheets.


0

From Erik Tyler's answer-as-a-comment (with some reformatting): Just format the date cell/range as a date in the format of your choice. An easy way, given your particular sheet, would be to click on a cell that contains a date already in the format you like (e.g., F2), click the paintbrush icon in the menu (far left), then click the new cell (or click and ...


1

If the cells A2 and B2 already have the dates in the format you need, you can just use this: ="Today is " & trim(A2) & ", and tomorrow is " & trim(B2) See this answer for an explanation of how date and time values work in spreadsheets.


1

I figured it out! I can use TEXT(). Using the example from last time, I would use the following: ="Today is, " & TEXT(A2, "MM/DD/YYYY") & " Tomorrow is " & TEXT(B2, "MM/DD/YYYY") & "!"


0

Use this to detect #N/A errors: =arrayformula( if( isna(F1:F), "error", F1:F ) ) The formula will give error when a cell in column F is an #N/A error, and the value in column F otherwise.


0

Tl;Dr replace getValues() by getDisplayValues() Explanation getValues() returns JavaScript Date objects for cells having a date, time, date-time and duration value. The value show in your form is the default conversion of the Date object into a string. getDisplayValues() returns the cell displayed value as string. Related How to deal with three different ...


0

I found a way around this by doing the following. I'm getting the time zone of the current sheet and the sheet that I'm copying from, changing the current one to match the source and then changing it back after copying. Seems to work great! // Super annoying hack... Copying dates across time zones changes the timestamps and sometimes changes the dates so we'...


1

The "problem" occurs because the cell format of the cell containing the custom function. One way to solve this is to change the format from date to duration. Related Entering mm:ss: milliseconds into Google Sheets for time duration (arithmetic) How to format the time in this Google Apps Script


Top 50 recent answers are included