Forum Discussion
Autofill dates while skipping cells (without formatting)
Hi everyone, new (ish) to Microsoft Excel and I'm having some trouble with the Autofill tool. I have an old schedule that I'd like to update the dates on, without having to manually input each date. I read online that you could autofill while skipping certain cells by pressing ctrl (cmd for me bc ios), but when I do that, the shortcut Autofill icon disappears. When I search for Autofill, I can't find the option of Fill w/o Formatting. I can always go back and individually correct each cell color and date, but I'm sure there has to be some ways to cut down on time, I just don't know where to look. The dates in the photo were the ones I tried messing around with, but as you can see I'm unable to keep the original cell colors.
I'd do the following. It'll take a bit of time, but it is a one-time action.
Let's say the cell with Week 1 is B3
Enter the start date in the cell next to it, i.e. in C3.
In C4, enter the formula =C3+1 and fill down to C9.
In D3, enter the formula =C3 and apply the custom number format dddd to D3.
Fill down to D9.
In the cell next to the cell with Week 2, i.e. in C11, enter the formula =C3+7.
In D11, enter the formula =D11 and format as dddd.
Select C11:D11 and copy/paste to C12:D17, then to C19:D25, etc.
In the future, you'll only have to change the start date in C3 and all other dates will be adjusted automatically.
I'd do the following. It'll take a bit of time, but it is a one-time action.
Let's say the cell with Week 1 is B3
Enter the start date in the cell next to it, i.e. in C3.
In C4, enter the formula =C3+1 and fill down to C9.
In D3, enter the formula =C3 and apply the custom number format dddd to D3.
Fill down to D9.
In the cell next to the cell with Week 2, i.e. in C11, enter the formula =C3+7.
In D11, enter the formula =D11 and format as dddd.
Select C11:D11 and copy/paste to C12:D17, then to C19:D25, etc.
In the future, you'll only have to change the start date in C3 and all other dates will be adjusted automatically.
- This is so helpful, thank you so much! It's much more efficient than what I've been doing, which is manually inputting everything.
