How to join date and time values into datetime and set it to a cell?

Question

I want to concatenate two cell values. Example:

Cell Date has Value: 5/11/2020. Cell Time has Value:14:00. Final Cell should be: 2020-05-11 14:00:00.

The way I am doing this is with:

sheetDestination.getRange(9,7).setValue(shiftDate + shiftTime);

The output ends up being:

Mon May 11 2020 00:00:00 GMT-0400 (Eastern Daylight Time)Range.

function shiftDuration () {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheetDestination = spreadsheet.getSheetByName("NewToImport");
  
    //Time Diff
    var timeStartforShift = sheetDestination.getRange(2, 4).getDisplayValue(); //start time
    var timeEndforShift = sheetDestination.getRange(5, 5).getDisplayValue(); //end time
    var shiftDuration = Math.floor(parseInt(timeEndforShift) - parseInt(timeStartforShift)); 
  
    //create a new row to test concatenating date and time
    var rangeDurantionToCopy = sheetDestination.getRange(1, 1, 2, 
    sheetDestination.getMaxColumns());
    rangeDurantionToCopy.copyTo(sheetDestination.getRange(8,1));
    var shiftEndTime = sheetDestination.getRange(9,5).setValue(timeStartforShift + (shiftDuration * 60));
    var shiftDate = sheetDestination.getRange(9,3).getValue();//get the Date for the shift
    sheetDestination.getRange(9,7).setValue(shiftDate + shiftEndTime);
}

Below is the function. I am adding a picture of the sheet. Basically the schedule shows a shift (4-hour shift), each row indicates 1 hour for the same shift.

I am trying to make it 1 row with start and end time which to show is a 4 hours shift. For example from 10 am - 2 pm, and there is a cell that needs to have the Date + Time (which is outputting so wrong!)

Answer 1

This is a matter of your data, not your code, though I don't want to comment on the code since it's written in googlish, which I don't understand.

When you open a new worksheet all its cells are pre-formatted as "General" which means that Excel will guess what kind of data you enter and apply a suitable format accordingly. If you enter something that Excel recognizes as a date Excel will apply a date format. The same will happen if Excel recognizes the entry as Time. Take note, however, that you can over-ride this automation in many ways and we don't know what you have done in your worksheet.

When Excel decides that you entered a date (or you formatted the cell as Date) Excel will convert your entry to a number. The number for today's date is 44057. Tomorrow will be 44058 etc, counting from January 1, 1900.

Time is expressed as a fraction of a day, meaning a fraction of 1. 0.5 is half a day = 12 noon. If you enter 18:00 or 6:00 PM Excel will store the cell's value as 0.75. It follows that [Date] + [Time] = [Date/Time]. 44057 + 0.75 = 44057.75.

If you enter such a value in a cell - as a number (you can try this same one) - what Excel displays in the cell depends upon the cell formatting. If you set a cell format of *dd mmm yyyy HH:mm:ss" Excel will display 14 Aug 2020 18:00:00 or perhaps 14 Aug 2020 06:00:00 PM, depending upon your Regional Settings. I suppose GoogleSheets will do the same.

If you entered "44057.75" Excel will recognize a number, not a date. You need to apply a Date format to change the display. But if you entered "14 Aug 2020 18:00" Excel will recognize the Date/Time value. To make the cell display its value format the cell as "General" or give it a number format.

Answer 2

In order to troubleshoot you should log variables and their type at strategic positions

Sample:

function shiftDuration () {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetDestination = spreadsheet.getSheetByName("NewToImport");
  var timeStartforShift = sheetDestination.getRange(2, 4).getDisplayValue(); //start time
  // the following returns "string"
  Logger.log(typeof timeStartforShift);
  var timeEndforShift = sheetDestination.getRange(5, 5).getDisplayValue(); //end time
  var shiftDuration = Math.floor(parseInt(timeEndforShift) - parseInt(timeStartforShift)); 
  var rangeDurantionToCopy = sheetDestination.getRange(1, 1, 2, sheetDestination.getMaxColumns());
  //careful with this line! You ar overwriting getRange(9,3)!!!
  //   rangeDurantionToCopy.copyTo(sheetDestination.getRange(8,1));
  //the following returns "10:00"120:
  Logger.log(timeStartforShift + (shiftDuration * 60));
  var shiftEndTime = sheetDestination.getRange(9,5).setValue(timeStartforShift + (shiftDuration * 60));
  var shiftDate = sheetDestination.getRange(9,3).getValue();
  // the following returns `object`  - a date object
  Logger.log(typeof shiftDate); 
  // the following returns `object`  - a date object
  Logger.log(typeof shiftEndTime);
  sheetDestination.getRange(9,7).setValue(shiftDate + shiftEndTime);
}

• The logs in the sample above show you that you are trying to sum variables of different types
• This will result in strings, that is text that has been patched together
• You cannot perform mathematical operations with variables that are not numbers - this also applies for date objects

You have three options:

• Retrieve your date and time separetely as strings (e.g. with getDisplayValues()) and "patch them together"
• Convert the date objects with Javascript Date functions like getTime() into ms - that is numbers, sum the seconds and convert the sum back to a date object
• Modify the date object with methods like setHours() and setMinutes()

The latter approach might be the easiest in your case and has the benefit of returning you a valid date rather than text into your spreadsheet.

Sample:

function shiftDuration () {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetDestination = spreadsheet.getSheetByName("NewToImport");
  var timeStartforShift = sheetDestination.getRange(2, 4).getDisplayValue(); //start time
  var timeEndforShift = sheetDestination.getRange(5, 5).getDisplayValue(); //end time
  var shiftDuration = Math.floor(parseInt(timeEndforShift) - parseInt(timeStartforShift)); 
  var hours = timeEndforShift.split(":")[0];
  var minutes = timeEndforShift.split(":")[1];
  var shiftDate = sheetDestination.getRange(9,3).getValue();
  Logger.log("before adding the time: " + shiftDate); 
  shiftDate.setHours(hours);
  shiftDate.setMinutes(minutes);
  Logger.log("after adding the time: " + shiftDate); 
  sheetDestination.getRange(9,7).setValue(shiftDate).setNumberFormat("yyyy-MM-dd hh:mm");
}