0

So I am trying to create a basic daily finances tracker using google sheets. And I want to be able to have it so that yearly tabs reference a template sheet that allows you to change the year on each new tab.

However I can't figure out how to add a changeable year value in a seperate cell to the month and day of the main column and have it autofill out the month and days so that if you change the year it will change the year value all the way down the column.

For example "01/01/" & A2, would equal 01/01/2022, and still have the option to change the year and have it go all the way through the chart

I have tried using Indirect, and concantenation using & but possibly not correctly, any help would be appreciated

2
  • I can't envision the setup of your sheet. What is the exact range where you want the output? Is the year actually in cell A2? How many dates should be generated: one? Dates for the entire year?
    – Erik Tyler
    Oct 22 '21 at 0:10
  • The year is in A2 seperated from the rest of the date so that it is easily changeable, and dates for the whole year would be generated, so that each day will follow 01/01/year, 02/01/year, Etc. Oct 22 '21 at 0:17
1

See my comment on your original post.

That said, assuming you want to generate dates for the entire year number listed in A2:

1.) Make sure you have an empty range that can receive 365 dates (e.g., A3:A or B2:B).

2.) Place the following formula in the topmost cell of the above output range:

=SEQUENCE( ("12/31/"&A2) - ("1/1/"&A2) + 1, 1, "1/1/"&A2)

3.) Select the output range and set the formatting to the date format of your choice (Format > Number > date format of choice).

The formula creates a sequence x rows by y columns starting at z (and, in this case, moving by the default increment of 1, which can be set as a fourth parameter in other cases):

x (i.e., number of rows) is found by subtracting the first day of the target year from the last day of the target year and adding 1. Doing it this way is necessary, in order to account for Leap Years.

y is 1 column.

z starts the SEQUENCE at the first day of the target year.

This version would produce the same results:

=SEQUENCE( ("1/1/"&A2+1) - ("1/1/"&A2), 1, "1/1/"&A2)

4
  • yes that has worked thank you for the assistance Oct 22 '21 at 0:35
  • Great. If you would, please take a moment to mark my solution post as "Best Answer." That helps the contributor community to quickly see which issues have been fully resolved; and it helps future site visitors like yourself quickly find the answers that are most relevant to their own issues.
    – Erik Tyler
    Oct 22 '21 at 0:58
  • With a value of 2021 in cell A2: For the first suggested formula, I'm getting an error message "Function MINUS parameter 1 expects number values. But '12/31/2021' is a text and cannot be coerced to a number." OTOH, the second suggested formula works like a dream. Thoughts?
    – Tedinoz
    Oct 24 '21 at 23:50
  • I'd have to see it myself, @Tedinoz. (You know how that goes.) Both work for me. If you have a way you feel comfortable with to share a sheet illustrating the issue, I'll take a look. You can always wrap the concatenated dates in DATEVALUE( ).
    – Erik Tyler
    Oct 25 '21 at 0:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.