When I use the formula here How to Manually Add Week Data Learn how to add historical week data for transactions your manually adding. the week is incorrect. The week that displays is the Sunday following the transaction date, not the Sunday prior. I can’t f…

I just tried it and there are two likely failure modes, @lava890 … 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. If you paste the formula from the community, you will likely change the quotations around WEEK to smart …

:wave: @lava890 ! You might check out this thread and see if it helps? [image] Using Tiller's formula for week/month data results in error Google Sheets Hi @Blake , You could use the WEEKNUM() function to create a week number column based on whatever week starting day yo…

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 c…

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…

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,"W…

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

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

[image] eoghann: =arrayformula(if(row($A1:$A)=1,“Week”, iferror(date(YEAR($A1:$A),month($A1:$A),day($A1:$A)-weekday($A1:$A)+1)))) 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 bi…

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.

How to manually add week data

Spreadsheets Google Sheets

heather May 20, 2021, 4:36pm 2

@lava890 !

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

Topic		Replies	Views
Manually add weekly data in Excel Microsoft Excel	5	977	September 27, 2022
How to calculate "Month" and "Week" columns in Transaction sheet? Google Sheets how-to	7	2326	December 11, 2023
Using Tiller's formula for week/month data results in error Google Sheets	16	1698	March 31, 2021
Excel Week Data issue with first day of the week value Microsoft Excel excel , troubleshooting	7	98	September 27, 2024
Better formula for populating Month and Week when preparing bulk entries gSheets Templates how-to	2	405	June 10, 2022

How to manually add week data

Related topics