Manually add weekly data in Excel

I’m trying to add historical data older than what Tiller imports from my account and I saw this article showing “How to manually add weekly data”, but it only brings the following formula to Google Sheets:

=arrayformula(if(row(B1:B)=1,“Week Array”,if(B1:B<>“”,B1:B-WEEKDAY(B1:B)+1,“”))).

Anyone knows the formula to do the same in excel? (I’d actually create the formula as I did for the “month”, but I couldn’t understand the logic being used for that column…)
Thanks

1 Like

I guess I found the formula that is consistent with what I’m seeing in the values imported automatically. Assuming the date is in a column called “Date”. here’s what worked for me:

=[@Date]-WEEKDAY([@Date],2)+1

Let me know if someone had a different result.

1 Like

This basically worked for me. I think because I had not formatted my data as a table though, I had to change the formula to this:
=A2-WEEKDAY(A2,2)+1

And then dragged the formula to the bottom of the dataset, then copied and paste-specialed it in.

The convention is for the “Week” values to fall on a Sunday.

In Excel, you don’t need to add the one.
To match Tiller Feeds “Week” data, I am using this in Excel:
=[@Date]-WEEKDAY([@Date],2)

I don’t think it is possible to create a spill-range-based formula within an Excel table.

@randy do you mean equivalent of Google Sheets arrayformula?

In my experience in Excel Tables if you fill in a formula like this in the first row in the column it will auto fill down to the other rows.

“Spill Ranges” are sort of the Excel equivalent of ARRAYFORMULA()
… but someone please correct me if my terminology is wrong… :person_shrugging:

When I try to use a spill range inside the Transactions Excel table, I get this error:

The formula you typed could return multiple values which is not supported in tables.
Would you like to use the following formula instead?
=[@Date]-WEEKDAY ([@Date],2)

image

Formulas within Excel tables are allowed which is the workaround… the down side is that the formula must be extended as rows are added.