Skip to content

Instantly share code, notes, and snippets.

@jack-williams
Last active January 19, 2024 05:13
Show Gist options
  • Save jack-williams/4f0c470909ebe01041d28427bdbdff79 to your computer and use it in GitHub Desktop.
Save jack-williams/4f0c470909ebe01041d28427bdbdff79 to your computer and use it in GitHub Desktop.
Excel Date Library
/**
* Returns the day of the week for the provided date,
* where Monday is the first day.
*/
dayOfWeek = LAMBDA(datetime, WEEKDAY(datetime, 2));
/**
* Returns the name for the day of the week for the provided date.
*/
dayOfWeekName = LAMBDA(datetime, TEXT(datetime, "dddd"));
dayOfYear = LAMBDA(datetime, datetime - DATE(YEAR(datetime), 1, 1) + 1);
daysInMonth = LAMBDA(datetime, DAY(EOMONTH(datetime, 0)));
daysInYear = LAMBDA(datetime, DATE(YEAR(datetime) + 1, MONTH(datetime), DAY(datetime)) - datetime);
endOfQuarter = LAMBDA(datetime, EOMONTH(DATE(YEAR(datetime), ROUNDDOWN(MONTH(datetime) / 3, 0) * 3, 1), 0));
endOfWeek = LAMBDA(datetime, datetime + 7 - WEEKDAY(datetime));
endOfYear = LAMBDA(datetime, DATE(YEAR(datetime), 12, 31));
quarterOfYear = LAMBDA(datetime, ROUNDDOWN(MONTH(datetime) / 3, 0) + 1);
startOfMonth = LAMBDA(datetime, DATE(YEAR(datetime), MONTH(datetime), 1));
startOfQuarter = LAMBDA(datetime, DATE(YEAR(datetime), ROUNDDOWN(MONTH(datetime) / 3, 0) * 3 + 1, 1));
startOfWeek = LAMBDA(datetime, datetime - WEEKDAY(datetime) + 1);
startOfYear = LAMBDA(datetime, DATE(YEAR(datetime), 1, 1));
weekOfMonth = LAMBDA(datetime, ROUNDUP(DAY(datetime + 7 - WEEKDAY(datetime)) / 7, 0));
weekOfYear = LAMBDA(datetime, WEEKNUM(datetime));
// Hidden test function
_do_test = LAMBDA(t,
HSTACK(
VSTACK(
"dayOfWeek",
"dayOfWeekName",
"dayOfYear",
"daysInMonth",
"daysInYear",
"endOfQuarter",
"endOfWeek",
"endOfYear",
"quarterOfYear",
"startOfMonth",
"startOfMonth",
"startOfQuarter",
"startOfWeek",
"startOfYear",
"weekOfMonth",
"weekOfYear"
),
VSTACK(
dayOfWeek(t),
dayOfWeekName(t),
dayOfYear(t),
daysInMonth(t),
daysInYear(t),
endOfQuarter(t),
endOfWeek(t),
endOfYear(t),
quarterOfYear(t),
startOfMonth(t),
startOfMonth(t),
startOfQuarter(t),
startOfWeek(t),
startOfYear(t),
weekOfMonth(t),
weekOfYear(t)
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment