Answer the question
In order to leave comments, you need to log in
Why is the date calculated incorrectly?
Good day!
I have created a script in Google spreadsheets that is needed to automatically calculate the completion date of the order based on the date of dispatch, the specific artist and the type of work. The script at first glance works, but the problem appears when the original date is outside the current month. So, for example, if the total order completion time is 5 days, the dispatch date is 08/31/2018 and the current month is September, respectively, the completion date will be calculated as 04/10(!).2018. Conversely, with the same parameters and the submission date of 10/1/2018, the completion date will be calculated as 09/06(!).2018
I'm still new to javascript and I can't figure out why this is happening. Please tell me what is the problem.
A function that calculates the date and is called in the onEdit of the date, artist, and work type cells:
function modifyDate(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Лаборатории');
var datasheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Цены и сроки');
var ColumnC = sheet.getRange(2, 3, sheet.getLastRow()-1, 1); //вид работы
var cValues = ColumnC.getValues();
var ColumnD = sheet.getRange(2, 4, sheet.getLastRow()-1, 1); //исполнитель
var dValues = ColumnD.getValues();
var ColumnA = sheet.getRange(2, 1, sheet.getLastRow()-1, 1); //дата отправки
var aValues = ColumnA.getValues();
//столбец N с отметкой о завершении заказа для проверки в условии
var ColumnN = sheet.getRange(2, 14, sheet.getLastRow()-1, 1);
var nValues = ColumnN.getValues();
var cdek = 6;
var i2date = new Date();
var Ivanov = datasheet.getRange('I1').getValue(); //фамилия исполнителя
var R5 = datasheet.getRange('A2').getValue(); //вид работы
var I2 = datasheet.getRange('I2').getValue(); //соответствующее количество дней
for (var i = 0; i < aValues.length; i++) {
var aData = new Date(aValues[i][0]);
var cData = new String(cValues[i][0]);
var dData = new String(dValues[i][0]);
var nData = new String(nValues[i][0]);
if (cData == R5 && dData == Ivanov && I2!='' && nData == '') {i2date.setDate(aData.getDate()+cdek+I2);
sheet.getRange(i + 2, 11, 1, 1).setValues()}
else {}
}
function onEdit(event){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Лаборатории');
var range = event.range;
if (range.getRow() >= 2 && range.getColumn() == 1){
modifyDate();
} else {}
}
Answer the question
In order to leave comments, you need to log in
The answer to this question is given on ruSO https://ru.stackoverflow.com/q/882107
The main idea is to use the base units for calculating dates (milliseconds) and not their derivatives (days, hours, minutes).
I would like to add that
> new String('Hello world!') == 'Hello world!'
<· true
> new String('Hello world!') === 'Hello world!'
<· false
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question