SOLVED

Excel Date Autofill

Copper Contributor

Hi,

 

Trying to autofill sequential dates in excel, despite altering how the series is filled, I still have the same results.

 

Clicking and dragging increases the year rather than date:

 

MONTUEWEDTHUFRISATSUN
27/2/201727/2/201827/2/201927/2/202027/2/202127/2/202227/2/2023

 

Dates need to be in UK format: date/month/year

 

Thanks!

 

Ben

18 Replies

Hello Ben

 

When you release the mouse after filling the cells a smart tag appears with further options.

 

Hi There, I see the smart tag, the options I have there are: copy cells / fill series / fill formatting only / fill without formatting None of these give the desired result. Thanks

Are your regional settings on UK date format?FillDates.JPG

Hi Sergei,

 

Where do I find the option to change this?

 

 

If not, enter first date in <your> date format (in my case ISO), press Ctrl+1 and enter in Custom format UK one for dates

DateFormat.JPG

 

After that right click and drag the corner, press Fill Days

 

FillDays.JPG

Here they are

 

Filled.JPG

If not, enter first date in <your> date format (in my case ISO), press Ctrl+1 and enter in Custom format UK one for dates

DateFormat.JPG

 

After that right click and drag the corner, press Fill Days

 

FillDays.JPG

Here they are

 

Filled.JPG

Untitled.png

 

Hi Sergei,

 

Not sure if it's something to do with the version I have, but I don't have the 'fill days' option available.

Hi,

 

Please do the following

- be sure the date under the Mon is in proper date format

- click on this cell, it'll be selected and you'll see the dot in right bottom corner of the selection

- focus the mouse on this dot, you will see the cross

- right click the mouse on this cross (it will disaapear), keep right button pushed and drag the selection to the right till the cell under Sun

- now release the right button, menu with "Fills" will appear

- click on Fill Days

 

Thanks again Sergei, for some reason this option is greyed out when I right click + drag.

best response confirmed by benlewis75 (Copper Contributor)
Solution

That means in your cell under the Mon is the text, not date. To check you may click Ctrl+1 on this cell and format as the number. It shall show 42793 for February 27, 2017. Otherwise it'll be the text you entered.

 

You may do in opposite way - format this cell as number, insert 42793, after format as "dd/mm/yyyy". And after that fill the days.

 

If you are not sure what's you default date format take any empty cell, format is as Date (doesn't matter in which form), enter any number in this cell and check the formula bar - it shows the date in your default format.

Fixed! Thanks Sergei!

I need to fill a list of headers with dates every 7 days. I've never had a problem until I bought Office 2016 pro and now using fill/series and entering 7 in the step choice, doesn't work. Help

Hi Des,

 

In Excel 2016 Pro it's also no problem with that if your dates are in correct format and proper options are selected for Fill->Series (i.e.Series In = Rows)

Fill7Days.JPG

 

Hi Sergei Thanks for your reply. I have followed that procedure but it didn't work - I received the error message thats attached as a screen print. Please help. I have checked and all of the cells are merged and are clearly the same size.

 

Hi Sergei, screen shot of error message below. I can follow this procedure and it works in Office 2010.  

 

     101 Screen shot 08-2017.jpg

Thanks, Des

Hi Des,

 

Not sure how it was in 2010, never use merged cells. In 2016 - yes, Series menu with defined increment doesn't work on merged cells.

 

However, simple Fill days, months, etc as well as Fill Series works here - enter into the first merged cell some date, in next cell the date which is on 7 days more. Select these two cells, drag by right mouse till end of your range with merged cells , Fill Series from menu.

MergedSeries01.JPG

 

MergedSeries02.JPG

 

 

Hi Sergei, That works - thanks. Des

@benlewis75 

You can easily autofill dates in Excel Sheet. Here is the step-by-step easy guide you can follow https://tutorialdeep.com/knowhow/autofill-dates-months-time-excel/.

1 best response

Accepted Solutions
best response confirmed by benlewis75 (Copper Contributor)
Solution

That means in your cell under the Mon is the text, not date. To check you may click Ctrl+1 on this cell and format as the number. It shall show 42793 for February 27, 2017. Otherwise it'll be the text you entered.

 

You may do in opposite way - format this cell as number, insert 42793, after format as "dd/mm/yyyy". And after that fill the days.

 

If you are not sure what's you default date format take any empty cell, format is as Date (doesn't matter in which form), enter any number in this cell and check the formula bar - it shows the date in your default format.

View solution in original post