Question on DATE(x) function referencing another location for date instead
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))
Answers
-
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, ………………………..)
-
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)))) -
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?
-
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? -
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!