Copying a date/time formatted cell, it changes hours or minutes value in the target cell

Question

Please, just to understand where I made a mistake. I have a spreadsheet called "source_list", there is a single sheet inside. In that sheet the cell R4 is formatted as "dd/mm/yy HH:MM". Using a script I copy the data from this sheet to spreadsheet "dest_sprd" with various sheets inside,the script copies as well the R4 cell with the statement:

ws.getRange("T1").setValue(wsSrc.getRange("R4").getValue())

T1 is a cell with the same format "dd/mm/yy HH:MM".

In the cell T1 I can see that the date is correct but the hour isn't, it differs by about 30 minutes from the R4 cell. In another case with the same copy statement, but different spreadsheet, the difference is 1 hour. The workaround I found is to format cell R5 as a plain number and put it "=R4", then the copy statement is

ws.getRange("T1").setValue(wsSrc.getRange("R5").getValue())

Now I have the correct date and time in cell T1.

Why it happens like that? I missed something? Thank you for your help.

Answer 1

I cannot reproduce your results:

Using this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.getRange("A4").setNumberFormat("dd/MM/yy hh:mm")
  sh.getRange("A4").setValue(new Date());
  const dss = SpreadsheetApp.openById(gobj.globals.test1id);
  const dsh = dss.getSheetByName("Sheet0");
  dsh.getRange("B4").setNumberFormat("dd/MM/yy hh:mm")
  dsh.getRange("B4").setValue(sh.getRange("A4").getValue())
}

I get the same date and time

Answer 2

About the following your question,

Why it happens like that? I missed something?

About I have a spreadsheet called "source_list", there is a single sheet inside. In that sheet the cell R4 is formatted as "dd/mm/yy HH:MM". Using a script I copy the data from this sheet to spreadsheet "dest_sprd" with various sheets inside,the script copies as well the R4 cell with the statement:, in your actual situation, if the Spreadsheet including "source_list" sheet is different from the Spreadsheet including "dest_sprd", I guessed that the timezone might be different between these Spreadsheets. I guessed that this might be the reason for your current issue.

If you want to avoid this issue, how about the following modification patterns?

Pattern 1:

In this pattern, the same timezone is used in both the Spreadsheet including "source_list" and the Spreadsheet including "dest_sprd".

Unfortunately, I do not know your actual Spreadsheet. So, this script is a simple sample script. Please be careful about this.

In this case, please copy and paste the following script to the script editor of Spreadsheet including "source_list" sheet.

function sample1() {
  const ss1 = SpreadsheetApp.getActiveSpreadsheet();
  const ss2 = SpreadsheetApp.openById("###"); // Please set the Spreadsheet ID of Spreadsheet including "dest_sprd" sheet.
  const wsSrc = ss1.getSheetByName("source_list");
  const ws = ss2.getSheetByName("dest_sprd");

  const ss2Timezone = ss2.getSpreadsheetTimeZone(); // Store current timezone of ss2.
  ss2.setSpreadsheetTimeZone(ss1.getSpreadsheetTimeZone()); // Change timezone of ss2 to the timezone of ss1.
  ws.getRange("T1").setValue(wsSrc.getRange("R4").getValue());
  ss2.setSpreadsheetTimeZone(ss2Timezone); // Change timezone of ss2 to the original timezone of ss2.
}

• In this sample script, before the date object is put to the "dest_sprd" sheet of "ss2", the timezone is set to that of "ss1". And, the value is put into the cell "T1". And then, the timezone of "ss2" is changed to the original timezone of "ss2". By this flow, the value can be kept with the original timezone of "ss2".

Pattern 2

About The workaround I found is to format cell R5 as a plain number and put it "=R4", then the copy statement is in your question, in this case, I guessed that the timezone offset is reflected. When this is used, the sample script is as follows.

Unfortunately, I do not know your actual Spreadsheet. So, this script is a simple sample script. Please be careful about this.

In this case, please copy and paste the following script to the script editor of Spreadsheet including "source_list" sheet.

function sample2() {
  const ss1 = SpreadsheetApp.getActiveSpreadsheet();
  const ss2 = SpreadsheetApp.openById("###"); // Please set the Spreadsheet ID of Spreadsheet including "dest_sprd" sheet.
  const wsSrc = ss1.getSheetByName("source_list");
  const ws = ss2.getSheetByName("dest_sprd");

  const value = wsSrc.getRange("R4").getValue();
  const unixTimeWithOffset = value.getTime() - (1000 * 60 * value.getTimezoneOffset());
  const serialNumber = (unixTimeWithOffset / 1000 / 86400) + 25569; // Ref: https://stackoverflow.com/a/6154953
  ws.getRange("T1").setValue(serialNumber);
}

Pattern 3:

In this pattern, the timezone of "ss2" is directly set to the date object.

Unfortunately, I do not know your actual Spreadsheet. So, this script is a simple sample script. Please be careful about this.

In this case, please copy and paste the following script to the script editor of Spreadsheet including "source_list" sheet.

function sample3() {
  const ss1 = SpreadsheetApp.getActiveSpreadsheet();
  const ss2 = SpreadsheetApp.openById("###"); // Please set the Spreadsheet ID of Spreadsheet including "dest_sprd" sheet.
  const wsSrc = ss1.getSheetByName("source_list");
  const ws = ss2.getSheetByName("dest_sprd");

  const range = wsSrc.getRange("R4");
  const v = Utilities.parseDate(range.getDisplayValue(), ss2.getSpreadsheetTimeZone(), range.getNumberFormat().replace(/"/g, ""));
  ws.getRange("T1").setValue(v);
}

Note:

• From my guess, I could correctly replicate your situation. And, when I tested the above scripts, I confirmed that both scripts worked. If you tested these scripts, your expected result might not be able to be obtained, can you provide the sample Spreadsheets? By this, I would like to confirm it.

References:

• getSpreadsheetTimeZone()
• getSpreadsheetTimeZone()
• getTimezoneOffset()
• parseDate(date, timeZone, format)