Excel - how to- question.

17togo

I'm trying to create a record sheet for a 4 week roster period. What I need Excel to do is to auto populate the dates for that 4 week period. So for instance in the top right corner in a cell I put in the date that the roster period ends and I then want the dates to auto populate the dates up to that end of roster date in a column on the left hand side. First World problems I know!!!

I'm fairly useless with excel so apologies if this is a simple task to you more learned folk!! :-D

me_irl

Sorry if this isn't what you mean, but you can use "enable autocomplete...".

More here: https://support.office.com/en-us/article/fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db

waraf

Alternatively you can use this method...

1. Highlight the column you want to enter the dates in

2. Right click on that column and select Format Cells

3. On the Number tab, click on the category Date

4. Highlight the date format you want and click OK

5. Enter the start date of the roster period in the first cell of that column

6. Click on the cell so that it is highlighted

7. Place your mouse pointer over the bottom right hand corner of that cell. A little black cross should appear

8. When the little black cross appears, hold down the mouse button and drag down. The dates will automatically increment by one day

aoh

Are you saying you want Excel to fill in the other dates each time you update the last date?

If so, e.g. put your end date in A1.

Formula in B1 would be =A1-27 (first day of the four weeks)
Formula in B2 would be =A1-26 (second day of the four weeks)
.
.
.
Formula in B27 would be =A1-1 (second last day of the four weeks)
Formula in B28 would be =A1 (last day of the four weeks)

Any time you update A1, the rest should change themselves.

17togo

aoh wrote:

Are you saying you want Excel to fill in the other dates each time you update the last date?

Sorry if I'm not explaining it correctly. So on the top row I have the name, employee number, dept etc and at the end of this row (in this case it's cell x3) I have the roster end date (3/12) So then in cells A8 to A35 I need it to populate the dates from the 6/11 to 3/12.

I've to print out 40ish of these every 4 weeks and each one is saved individually so it'll just make life a bit easier than having to do it manually. Because then I'll literally be able to open up each record and just change the date in cell x3 and press print.
Again first World problem I know!

Excel really is something I'd love to know how to use properly but man it's feckin confusing!!

17togo

waraf wrote:

Alternatively you can use this method...

Thanks but it's not exactly what I need, I know how to do this alright. But if you see my last post I just need to be able to change the date in one cell and the rest will be done for me. Thanks for your reply though.

17togo

me_irl wrote:

Sorry if this isn't what you mean, but you can use "enable autocomplete...".

me_irl wrote:

More here:

Thanks the answer might be in there somewhere, I'll have a proper look at it later. Probably not ideal trying to do this after a few drinks last night! :-/

Danjamin1

Enter the end date in cell X3
In cell Y3 or somewhere on the sheet enter the number of days in the period
In cell A8 type =X3-Y3 - You'll need to make sure the cell is formatted as a date
In cell A9 type =A8+1 - Then just copy this formula down to cell A35 making sure every cell is equal to the cell above + 1 day.

This way you'll only ever have to change the end date in cell X3 & the number of days in the period in cell Y3

sKeith

you're close though.

if you have a date in B4 for example. the in B5, you put an =B4+1,
then flood fill that formula down to bottom.
Now, as soon as you change the date in the original location B4, all the others will change automagically.

17togo

Danjamin1 wrote:

This way you'll only ever have to change the end date in cell X3 & the number of days in the period in cell Y3

Danjamin1 wrote:

Enter the end date in cell X3 In cell Y3 or somewhere on the sheet enter the number of days in the period In cell A8 type =X3-Y3 - You'll need to make sure the cell is formatted as a date In cell A9 type =A8+1 - Then just copy this formula down to cell A35 making sure every cell is equal to the cell above + 1 day.

Thanks a mill this worked. I just had to reverse it and put the formula in a35 =a35-1 as I wanted the days filled up to that date! But excellent cheers for that!

aoh

17togo wrote: »

Sorry if I'm not explaining it correctly. So on the top row I have the name, employee number, dept etc and at the end of this row (in this case it's cell x3) I have the roster end date (3/12) So then in cells A8 to A35 I need it to populate the dates from the 6/11 to 3/12.

I've to print out 40ish of these every 4 weeks and each one is saved individually so it'll just make life a bit easier than having to do it manually. Because then I'll literally be able to open up each record and just change the date in cell x3 and press print.
Again first World problem I know!

Excel really is something I'd love to know how to use properly but man it's feckin confusing!!

Ok so your date goes into X3. Formula in A35 is "=X3" without the quotes.

Formula on A34 is "=A35-1" and then you copy that formula to all cells up to A8.