/**
* Calculates the number of days between dates in a range, with support for various date formats and inclusion/exclusion rules.
* @param {string|Range} dateRange A cell containing date range or direct date range string
* @return {number} The number of days between the dates
* @customfunction
*/
function DBC(dateRange) {
// Date format definitions
const dateFormats = [
{
name: "MM/DD/YYYY",
regex: /^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/,
parse: (match) => {
const month = parseInt(match[1], 10);
const day = parseInt(match[2], 10);
const year = parseInt(match[3], 10);
if (isValidCalendarDate(year, month, day)) {
return new Date(year, month - 1, day);
}
return null;
}
},
{
name: "DD/MM/YYYY",
regex: /^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/,
parse: (match) => {
const day = parseInt(match[1], 10);
const month = parseInt(match[2], 10);
const year = parseInt(match[3], 10);
if (isValidCalendarDate(year, month, day)) {
return new Date(year, month - 1, day);
}
return null;
}
},
{
name: "YYYY-MM-DD",
regex: /^(\d{4})-(\d{1,2})-(\d{1,2})$/,
parse: (match) => {
const year = parseInt(match[1], 10);
const month = parseInt(match[2], 10);
const day = parseInt(match[3], 10);
if (isValidCalendarDate(year, month, day)) {
return new Date(year, month - 1, day);
}
return null;
}
}
];
// Helper functions
function isValidCalendarDate(year, month, day) {
const d = new Date(year, month - 1, day);
return (
d.getFullYear() === year &&
d.getMonth() === month - 1 &&
d.getDate() === day
);
}
function tryParseDate(str) {
str = str.trim();
// Handle ambiguous numeric formats first
let m = str.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/);
if (m) {
const n1 = parseInt(m[1], 10);
const n2 = parseInt(m[2], 10);
const year = parseInt(m[3], 10);
// If both numbers are > 12, invalid
if (n1 > 12 && n2 > 12) return null;
// If either number is > 31, invalid
if (n1 > 31 || n2 > 31) return null;
// If first number > 12, must be DD/MM/YYYY
if (n1 > 12 && n2 <= 12) {
if (isValidCalendarDate(year, n2, n1)) {
const d = new Date(year, n2 - 1, n1);
return { date: d, format: "DD/MM/YYYY", ambiguous: false };
}
return null;
}
// If second number > 12, must be MM/DD/YYYY
if (n2 > 12 && n1 <= 12) {
if (isValidCalendarDate(year, n1, n2)) {
const d = new Date(year, n1 - 1, n2);
return { date: d, format: "MM/DD/YYYY", ambiguous: false };
}
return null;
}
// If both numbers ≤ 12, ambiguous
if (n1 <= 12 && n2 <= 12) {
if (isValidCalendarDate(year, n2, n1)) {
const d = new Date(year, n2 - 1, n1);
return { date: d, format: "DD/MM/YYYY", ambiguous: true };
}
return null;
}
}
// Try other date formats
for (const fmt of dateFormats) {
const m = str.match(fmt.regex);
if (m) {
const d = fmt.parse(m);
if (d && !isNaN(d.getTime())) {
return { date: d, format: fmt.name, ambiguous: false };
}
}
}
// Try native Date parsing as last resort
const d = new Date(str);
if (!isNaN(d.getTime())) {
return { date: d, format: "Native", ambiguous: false };
}
return null;
}
function splitDates(input) {
// Allow zero or more spaces before/after separator
const sepRegex = /\s*(?:-|–|—|to|till|until|through|thru|and|&|~|≈|between|from)\s*/i;
let parts = input.split(sepRegex);
if (parts.length >= 2) {
return [parts[0], parts[1]];
}
return [null, null];
}
function extractDateAndInclusion(str) {
// Match date and optional inclusion/exclusion at the end
const match = str.trim().match(/^(.*?)(?:\s+(incl\.?|inclusive|excl\.?|exclusive)[\s\.\,\;\:\)]*)?$/i);
if (!match) return { dateStr: str.trim(), inclusion: null, explicit: false };
let dateStr = match[1].trim();
let inclusion = null;
let explicit = false;
if (match[2]) {
inclusion = /excl/i.test(match[2]) ? 'exclusive' : 'inclusive';
explicit = true;
}
return { dateStr, inclusion, explicit };
}
function daysBetweenUTC(dateA, dateB) {
const utcA = Date.UTC(dateA.getFullYear(), dateA.getMonth(), dateA.getDate());
const utcB = Date.UTC(dateB.getFullYear(), dateB.getMonth(), dateB.getDate());
return Math.abs((utcB - utcA) / (1000 * 60 * 60 * 24));
}
// Main processing
const input = dateRange.toString().trim();
if (!input) {
throw new Error('Empty input');
}
// Remove leading "from", "between", etc. for robust splitting
let cleanedInput = input.replace(/^\s*(from|between)\s+/i, '');
// Remove leading/trailing parentheses, brackets, or braces
cleanedInput = cleanedInput.replace(/^[\(\[\{]\s*|\s*[\)\]\}]$/g, '');
// Split into two date strings
const [fromStrRaw, toStrRaw] = splitDates(cleanedInput);
if (!fromStrRaw || !toStrRaw) {
throw new Error('Invalid date range format. Use format like "1/1/2024 - 1/31/2024"');
}
// Extract inclusion/exclusion for each date
const fromExtracted = extractDateAndInclusion(fromStrRaw);
const toExtracted = extractDateAndInclusion(toStrRaw);
// Parse dates
const fromParsed = tryParseDate(fromExtracted.dateStr);
const toParsed = tryParseDate(toExtracted.dateStr);
if (!fromParsed || !toParsed) {
throw new Error('Invalid date format');
}
// Handle inclusion/exclusion
const fromInclusion = fromExtracted.inclusion || 'inclusive';
const toInclusion = toExtracted.inclusion || 'inclusive';
// Calculate days
let diff = daysBetweenUTC(fromParsed.date, toParsed.date);
if (fromInclusion === 'inclusive') diff += 1;
if (toInclusion === 'inclusive') diff += 1;
diff -= 1;
if (fromInclusion === 'exclusive' && toInclusion === 'exclusive') diff -= 1;
return Math.max(0, Math.round(diff));
}