# How to manually add week data

When I use the formula here

the week is incorrect. The week that displays is the Sunday following the transaction date, not the Sunday prior. I can’t figure out how the formula works, so I’m not sure which number to change to make it display correctly.

Thanks

You might check out this thread and see if it helps?

Thanks for your response. That solution you linked gives the week number of the year, but not the starting date of the week in the same format as the transaction sheet.

I’m basically asking if it’s possible the formula is wrong. Whether I paste it into a test worksheet or my transaction sheet, it consistently gives me the incorrect week. It outputs the week following the transaction date, rather than the week of the transaction date. If it’s wrong, I can fiddle with it a couple different ways to make it output the correct week, but I don’t know which way is best.

Hi @lava890 ,
It does look like there is something wrong with the formula.
I don’t fully understand all the parts of the formula, but in order to make it output 1 week (or 7 days) earlier, I added a minus 7 to get the date 1 week earlier.

Here’s my revised formula:
`=arrayformula(if(row(\$A1:\$A)=1,"Week", iferror(DATE(YEAR(\$A1:\$A), 1, -3 + 7 * WEEKNUM(\$A1:\$A) - WEEKDAY(DATE(YEAR(\$A1:\$A), 1, 4), 1) + 1)-7)))`

Note the -7 near the end.

This revised formula might not be the cleanest way to solve the problem, but it seems to work in my testing. Does it work for you?

cc @heather

Jon

1 Like

Thanks for sharing a solution, @jono.

Thanks. I’ll use that modification, and hopefully someone can check and correct that formula if necessary.

On the surface, that formula does seem really convoluted. Maybe there’s a reason for that which I am missing. If I was to attempt a formula that does the same thing (return the first day of the week that a particular date sits in) this is roughly how I would do it.

`=arrayformula(if(row(\$A1:\$A)=1,"Week", iferror(date(YEAR(\$A1:\$A),month(\$A1:\$A),day(\$A1:\$A)-weekday(\$A1:\$A)+1))))`

Adding an explanation for what this does, basically the date formula needs Year, Month and Day. So for Year and Month I’m simply referring back to the original date field, but for Day, I take the day from the date field but then I use the WeekDay command to get the current day of the week (from 1 to 7 with 1 being Sunday), subtract that from the day of the year and add 1. This way I should always end up with the Sunday of the current week. That formula should work correctly even if crossing a year.

2 Likes

@eoghann ,
Your formula makes much more sense to me.
Thanks for sharing it.

Jon

Thanks for sharing, @eoghann. That formula looks solid.

I appreciate the help, but when I try this in Google Sheets, it gives a formula parse error. I’ve played around with it for around 20 minutes to no avail. Not a big deal, I am using the solution from @jono

I just tried it and there are two likely failure modes, @lava890

1. Did you point all of the range references to the correct column? For my sheet, I had to change all `\$A1:\$A` references to `\$B1:\$B`.
2. If you paste the formula from the community, you will likely change the quotations around WEEK to smart quotes (i.e. `“` instead of `"`).

The version below might past better…
`=arrayformula(if(row(\$A1:\$A)=1,"Week", iferror(date(YEAR(\$A1:\$A),month(\$A1:\$A),day(\$A1:\$A)-weekday(\$A1:\$A)+1))))`

Personally, I’m partial to this formula structure but it works the same:
`={"Week";arrayformula(iferror(if(isblank(\$B2:\$B),1/0,date(YEAR(\$B2:\$B),month(\$B2:\$B),day(\$B2:\$B)-weekday(\$B2:\$B)+1))))}`

2 Likes

I believe @randy is correct and the issue is the smart quotes that have crept in at some stage. I tried copying and pasting from here back to the original and it choked on the quotes.

One thing that can help, @eoghann, is enclosing any formulas you share in markdown `monospace` apostrophes.

=“Week”

versus

`="Week"`

Yes! It was the smart quotes!! Thank you. The formula seemed like it should work, and it was driving me crazy.

Also, perhaps you can update the formula on this page so it has the correct output How to Manually Add Week Data | Tiller Help Center

I have updated it @lava890 ! Thanks for catching that error. It’s been like that for sooooo long