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
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
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.
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…
$A1:$A references to $B1:$B.“ 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))))}
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
Thanks, glad I’m not crazy.
