Dates in my excel not filling sequentially only copying

[mwtbealwestnetcomau]

I wish to auto fill dates but my excel fill handle only copies the date, it does not fill sequentially. Can some one please advise

[#2]

@mwtbealwestnetcomau 

If the fill handle in Excel is only copying the date instead of filling it sequentially, it might be because the fill series option is disabled.

Here is how you can enable it:

1. Enter the starting date in a cell.
2. Click and drag the fill handle (the small square in the bottom-right corner of the selected cell) to the desired range where you want to fill the dates.
3. When you release the mouse button, a small options box should appear next to the filled cells.

If you don't see the options box, follow these steps to enable it:

1. Go to the "File" tab in the Excel ribbon.
2. Click on "Options" to open the Excel Options dialog box.
3. In the dialog box, select the "Advanced" tab.
4. Scroll down to the "Editing options" section.
5. Make sure the "Enable fill handle and cell drag-and-drop" option is checked.
6. Click on the "OK" button to save the changes and close the dialog box.

After enabling the fill handle and cell drag-and-drop option, try dragging the fill handle again to fill the dates sequentially. The options box should appear, allowing you to choose the "Fill Series" option. Selecting this option will fill the dates in a sequential manner based on the pattern identified by Excel.

Note that the availability of the fill series option may depend on the data type of the cells or the formatting applied to them. Ensure that the cells are formatted as dates or use the "Format Cells" option to set the desired date format before using the fill handle.

[#3]

@NikolinoDE 

Still no workie Nikolo

[#4]

@JoeAllen999 

If enabling the "Fill Series" option and ensuring that cells are formatted as dates did not resolve the issue of the fill handle only copying the date instead of filling it sequentially, you can try the following additional steps to troubleshoot and fix the problem:

1. Clear Cell Formats:
• Sometimes, cell formats or conditional formatting rules can interfere with Excel's fill handle behavior. Try clearing any formatting applied to the cells:
• Select the cells where you want to fill the date series.
• Right-click and choose "Format Cells."
• In the "Format Cells" dialog box, go to the "Number" tab.
• Select the "General" category.
• Click "OK" to remove any formatting.
• After clearing the formats, try using the fill handle again.
2. Check Workbook Calculation Settings:
• Incorrect calculation settings in your workbook can affect Excel's ability to fill dates sequentially. Ensure that your workbook is set to calculate automatically:
• Go to the "Formulas" tab on the Excel ribbon.
• In the "Calculation" group, check if "Automatic" is selected in the dropdown for "Workbook Calculation."
• If it is set to "Manual," change it to "Automatic."
3. Check for Excel Add-Ins:
• Excel add-ins, especially third-party add-ins, can sometimes interfere with Excel's default behavior. Disable any unnecessary add-ins:
• Go to "File" > "Options."
• In the Excel Options dialog box, go to the "Add-Ins" tab.
• Disable any add-ins that you do not need or suspect might be causing the issue.
• Click "OK" to save the changes and restart Excel.
4. Test in a New Workbook:
• Create a new, blank workbook.
• Enter a starting date in a cell.
• Try using the fill handle to see if it fills dates sequentially in the new workbook. This can help determine if the issue is specific to your current workbook.
5. Update or Repair Excel:
• Ensure that your Microsoft Office installation is up-to-date with the latest updates and patches. Outdated software can sometimes lead to unexpected issues. You can also try repairing your Excel installation if updating does not help.
6. Check for Data Validation Rules:
• If there are data validation rules applied to the cells where you are trying to fill dates, it could affect the fill handle behavior. Check for and remove any data validation rules if they exist.
7. Try a Different User Profile:
• Sometimes, user-specific settings can cause issues. Create a new user profile on your computer and see if Excel behaves differently when used from that profile.

If none of these steps resolve the issue, it is possible that there may be a more complex problem with your specific Excel installation or configuration. In such cases, you may want to reach out to your organization's IT support for further assistance. Otherwise I would be at my wits' end here too.

[#5]

@mwtbealwestnetcomau 

It seems that you want to clean the litter box more often than once a year. 😉 

Your screenshot is quite useful in identifying the problem.  Notice that your formula bar contains "Friday, 10/5/24".  But Excel does not display dates that way in the formula bar.  (You can, of course, make Excel display dates that way within cells.)  And the different "dates" in column A entries start with "Friday, ", even when those dates do not fall on a Friday; Excel will not make that kind of mistake.

Your problem is that you have text values in those cells.  Excel is autofilling, but it is incrementing only the numeric portion (two digits) it finds at the end of the text.

To get this correct, clear the contents of those "date" cells, and make sure column A is formatted as General (this format will be changed later).  Then enter just the first date (in your case, in the m/d/yy format, or yyyy-mm-dd format, or whatever comes naturally to you and is recognized by Excel as a date).  For the general case, enter the next expected date in the cell below that, to give Excel an idea as to the pattern of values to follow.  Then use the fill handle to autofill dates as far down as desired.

To get the day of week to appear within the cells in the format you seem to want, assign those cells (or simply, the entire column) the custom format "dddd, d/m/yy" (without the quotes, of course):

As dates/times are implicitly based on numbers, the cell values are displayed by default aligned to the right, but you can override that if desired.

[#6]

@SnowMan55 thanks for your thoughtful and detailed response. I will edit the dates as you suggested. Grateful.

[#7]

@NikolinoDE I've also found another -

8. Check there are no active filters on the sheet

(I have a running timesheet which, for long jobs, I filter so previous months are hidden. If the job overruns and I need to extend the sheet, I have to clear the filter before dates will fill correctly. )

[#8]

@mwtbealwestnetcomau 

Dear, 

If you type date this way i-e 15-Feb-2024, then auto series options will work. If you type date this way i-e 15-02-2024, only year series will be filled. Try it.Date Auto Series Solved 

[#9]

@NikolinoDE

Thank you - Check Workbook Calculation Settings - it was on manual! Now it works.

[#10]

Thanks so much, nothing else worked, would love an explanation of why it did tho !!