Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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))

Tags:

Answers

  • Community Champion

    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))))

  • Community Champion

    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?

  • Community Champion

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions