Question on DATE(x) function referencing another location for date instead

Konrad S.

Question:
I wanted to see if there is a possibility to reference the begin date to end date from another cell instead of inside the formula. MY attempts to use a reference to another location has failed.

For Example:
DATE([8]1) instead of DATE(2024, 12, 31)

Syntax I am using.
=SUMIFS({GL Datasheet_Amount}, {GL Datasheet_Project#}, [2]@row, {GL Datasheet_ClosedDate}, >=DATE(2024, 1, 1), {GL Datasheet_ClosedDate}, <=DATE(2024, 12, 31), {GL Datasheet_GL}, HAS(@cell, [7]2)) + SUMIFS({599940 Agency Spent_Amount}, {599940 Agency Spent (Closed) Range 1}, [2]@row, {599940 Agency Spent (Closed)_PayApprovalDate}, >=DATE(2024, 1, 1), {599940 Agency Spent (Closed)_PayApprovalDate}, <=DATE(2024, 12, 31))

Paul Newcome

As long as [8]1 is set as a date type column and has a date in it, you can use

DATE(YEAR([8]1), MONTH([8]1), DAY([8]1))

You can also save yourself a little bit of typing by calling out a year specifically instead of between Jan 1 and Dec 31 and having to enter the range twice.

=SUMIFS(………………., {Date Range}, IFERROR(YEAR(@cell), 0) = 2024, ………………………..)

Konrad S.

Hey Paul,

I am actually using (2) cells for start and end date:
Cell [Column42]1 is a start date (ex. 1/1/2025)
Cell [Column46]1 is an end date (ex. 12/31/2025)

When attempting to use the revised syntax with your DATE approach, I am now getting incorrect attribute set error. In theory it should work the same.

Original syntax:
=SUMIFS({GL Datasheet_Amount}, {GL Datasheet_Project#}, [2]@row, {GL Datasheet_ClosedDate}, >=DATE(2024, 1, 1), {GL Datasheet_ClosedDate}, <=DATE(2024, 12, 31), {GL Datasheet_GL}, HAS(@cell, [7]2)) + SUMIFS({599940 Agency Spent_Amount}, {599940 Agency Spent (Closed) Range 1}, [2]@row, {599940 Agency Spent (Closed)_PayApprovalDate}, >=DATE(2024, 1, 1), {599940 Agency Spent (Closed)_PayApprovalDate}, <=DATE(2024, 12, 31))

Revised syntax:
=SUMIFS({GL Datasheet_Amount}, {GL Datasheet_Project#}, [2]@row, {GL Datasheet_ClosedDate}, >=DATE(YEAR([Column42]1), MONTH([Column42]1), DAY([Column42]1)), {GL Datasheet_ClosedDate}, <=DATE(YEAR([Column46]1), MONTH([Column46]1), DAY([Column46]1)), {GL Datasheet_GL}, HAS(@cell, [7]2)) + SUMIFS({599940 Agency Spent_Amount}, {599940 Agency Spent (Closed) Range 1}, [2]@row, {599940 Agency Spent (Closed)_PayApprovalDate}, >=DATE(YEAR([Column42]1), MONTH([Column42]1), DAY([Column42]1), {599940 Agency Spent (Closed)_PayApprovalDate}, <=DATE(YEAR([Column46]1), MONTH([Column46]1), DAY([Column46]1))))

Paul Newcome

If you have the start and end dates already established in cells, it is easier to reference the cells directly assuming they are both in a date type column and house date type data.

=SUMIFS(………………………………………., {Date}, @cell >= [Column42]1, {Date}, @cell <= [Column46]1, …………….)

Have you ensured that [Column42] is set as a date type column?

Konrad S.

Hi Paul,

Yes, the Column 42 and 46 are designated as dates only.

Here is simplified formula to sum amount from another sheet using date column from another sheet using Start and End cell 42,1 and 46, 1. (cell 24,1 is 1/1/2025 and cell 46,1 is 12/31/2025)

=SUMIFS({GL Datasheet_Amount}, {GL Datasheet_ClosedDate}, @cell >= [Column42]1, @cell <= [Column46]1)

Am I getting application of this syntax incorrectly?

Paul Newcome

Not quite. You need to either duplicate the range for the second criteria, or you need to use an AND function to group the two criteria together.