Attached is a Test Sheet which shows results and expected results. The Test sheet has all of the functions needed to run the sheet.
The main function is supposed to return a range of dates generated from a starting date. Each date should be three months ahead of its preceding date.
The test uses two versions of the function, one using UTC date methods on a date object, the other using normal date methods on a date object.
The sheet named quarter in the "workbook" has the results. Sheet processing tests relies on these Named Ranges: StartDateQ, StartDateWeird.
Anyone who seeks to solve this need only look at the sheet named quarter, the scripts page holding the functions and have working knowledge of Google Apps Script and the Google Spreadsheet API.
The Problem
The array of returned dates are not as expected. In some cases, the dates look right but some of the dates have unwanted time data that can be seen only by selecting a cell. In some cases, the dates are not right, being off by a day.
Why is this a Problem?
In an actual spreadsheet, formulas that rely on date matching fail since some of the visual dates have associated actual time data. Since the time data doesn't match, even though visually, the dates look the same, matching fails. Matching failures break the sheet.
The Algorithm
- If there is at least a start date to process, get the number of quarters to generate dates using countAge().
- Initialize date object with start date sent from spreadsheet.
- Make an empty array to hold dates to return to the spreadsheet.
- For the tally of quarters, loop through one count at a time.
- Insert the latest date to the array.
- Move the date along to the next quarter until there are no more quarters for which to generate a date.
- Return the results to the spreadsheet
Functions in Use (internal API)
is(obj, type) //compare if object is the right type
countAge(dateString,dateString2) //count years between dates, aka age
TEST VERSIONS
quartersUTC(startDate,endDate) // custom function to get quarter dates from two dates — UTC Version
quartersPlain(startDate,endDate) // custom function to get quarter dates from two dates — Plain Version
COMMENTARY
Since neither test function relies on "date math", but instead relies on date setters, e.g.,
not this:
d = new Date();
d.setMonth(d.getMonth()) + 3;
but this:
if (latestDate.getMonth() == 0) {
latestDate.setMonth(3,1)
}
it does not seem obvious why the programming logic fails to produce the expected results.
THE FUNCTION
This is the plain version. The UTC version uses .setUTCmonth()
function quartersPlain(startDate,endDate) {
// Check if there is a date to process, if not, exit.
if (!is(startDate,"Date")) {
return "Missing date";
}
// Let's get the number of quarters
/* If there isn't an end date, we want the tally from the start date to now, aka the true age
otherwise, we want want the tally between two dates.
*/
var tally = 0;
if (!is(endDate,"Date")) {
tally = (countAge(startDate) * 4) + 3
}
else {
if (new Date(endDate) > new Date(startDate)) {
tally = (countAge(startDate,endDate) * 4)
}
else {
tally = (countAge(endDate,startDate) * 4)
startDate = endDate
}
}
// testing ...
// return tally
/*
1. Initialize date object with start date sent from spreadsheet.
2. Make an empty array to hold dates to return to the spreadsheet.
3. For the tally of quarters, loop through one count at a time.
4. Insert the latest date to the array.
5. Move the date along to the next quarter.
*/
// it's a logical fail needing to assign the date twice, kludgy
var latestDate = new Date(startDate);
/* testing ...
latestDate.setFullYear(latestDate.getUTCFullYear());
latestDate.setUTCMonth(latestDate.getUTCMonth());
latestDate.setDate(latestDate.getUTCDate());
*/
var dates = [ ];
for (var loop = 0; loop < tally; loop++) {
dates.push(new Date(latestDate));
if (latestDate.getMonth() == 0) {
latestDate.setMonth(3,1)
}
else if (latestDate.getMonth() == 1) {
latestDate.setMonth(4,1)
}
else if (latestDate.getMonth() == 2) {
latestDate.setMonth(5,1)
}
else if (latestDate.getMonth() == 3) {
latestDate.setMonth(6,1)
}
else if (latestDate.getMonth() == 4) {
latestDate.setMonth(7,1)
}
else if (latestDate.getMonth() == 5) {
latestDate.setMonth(8,1)
}
else if (latestDate.getMonth() == 6) {
latestDate.setMonth(9,1)
}
else if (latestDate.getMonth() == 7) {
latestDate.setMonth(10,1)
}
else if (latestDate.getMonth() == 8) {
latestDate.setMonth(11,1)
}
else if (latestDate.getMonth() == 9) {
latestDate.setMonth(0,1)
latestDate.setFullYear(latestDate.getFullYear() + 1)
}
else if (latestDate.getMonth() == 10) {
latestDate.setMonth(1,1)
latestDate.setFullYear(latestDate.getFullYear() + 1)
}
else if (latestDate.getMonth() == 11) {
latestDate.setMonth(2,1)
latestDate.setFullYear(latestDate.getFullYear() + 1)
}
}
return dates;
}
`
