Forum Discussion
Copy dates from another sheet
Hi! I have a spreadsheet to compile all of my work hours and every sunday, I add a new "blank week". I'd like to know if there's a way to create a formula that continues the dates automatically when I copy and paste it from the "Blank week" page instead of having to write them manually each time. I know I can insert the first date and double click the bottom right corner of the cell to apply them to the whole week, but I was wondering if the dates could just all add themselves instantly when I paste my template. Thanks a lot!
2 Replies
Alternatively, you could just insert four empty rows above the weekly template. Then, in the first date cell (that will then be B5) you enter:
=B1+1In cell B6 you enter:
=B5+1... and copy the cell down to the last day of the week.
Every week you can then copy rows 5 to 14 from the template and paste them directly under the previous week in the cumulative sheet for 2020.
In Week Template for Monday it could be formula
=DATE(2020,1,8)-WEEKDAY(DATE(2020,1,6)) + IF(ROW()>6,COUNTIFS($A$1:INDEX($A:$A,ROW()-1),"Dim",$B$1:INDEX($B:$B,ROW()-1),">1")*7,0 )and
=B1+1for next days.
With that entire rows could be copy/pasted from Week Template to Year tab. Of course, better to keep 2020 as parameter, i.e. use as some cell value.
Please check in attached.
