Share via

How To Make Dates Automatically Change With Formula?

Anonymous
2021-12-09T09:54:12+00:00

Normally the formula is simple. If the date in cell A2 is December 9. You just enter =A2+1 and it will automatically enter December 10 into the other cell. In case, I am trying to change the date by ONE DAY based on the date in another Sheet. In the screenshot below, the date is November 6 in cell A1 under the Sheet "NOV 6",

In the Sheet "NOV 7" in cell A1, I want to enter a formula that increases the date by ONE DAY. So in the sheet "NOV 7" in cell A1, the date would automatically be entered as November 7.

I tried to do it. But all of my formulas return an error message of "#SPILL!"... See the second screenshot below. The formula I entered was ='NOV 6'!A1:L2+1 but does not work.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. OssieMac 47,981 Reputation points Volunteer Moderator
    2021-12-09T10:42:05+00:00

    Place a leading @ sign in front of the formula as follows to suppress the Spill. Changes have occurred to accomodate the Spill.

    =@'NOV 6'!A1:L2+1

    See following link for more information on Spill

    https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531

    0 comments