Forum Discussion
Excel fill function only changes the year
Dear community,
I'm having an issue since a while with the Excel fill function.
When dragging cells over, it showed the Quick Analysis option - I've disabled this, now it shows nothing (even though the Fill tickbox is ticked in the Options - dont know why?)
But the biggest issue is that it does not fill dates.
I type
1.7.21, 2.7.21... (or 01.07.21, 1.7.2021, doesnt matter which date format), I drag it over, and it changed only the YEAR. So my column/row continues with 1.7.21, 1.7.22, 1.7.23.
I've changed to Text, General, Date, I've tried modifying the date to different date formats, I've looked into the system Date setup whether that's not synced... nothing helps. I've tried on new Excel sheets, it's the same.
I'm at the end of what I know how to do 😞
Any ideas? many thanks!
susannewalter You need to enter a valid date first, in the format that your Excel understands. You mentioned that your local setting are 7/1/2021 for the 1st of July. So, your systems is set-up to work with US style dates (M/D/Y). If you want to work with European style dates (D/M/Y), you can change the regional settings on your PC, or first enter a date US style and then use the custom format to change it.
There's not much more I can tell. Perhaps the link below helps.
12 Replies
susannewalter Most likely because the "dates" are in fact texts. What do you get when you enter a formula =ISNUMBER(...), where you enter a reference to a cell that contains a "date" between the brackets ?
Formatting a columns as Date isn't enough when the content itself are texts.
Rino_van_Eekelen it remains with the cell number, so for ex it says "=ISNUMBER(A6)"
It seems to do that with every cell I try to enter between the brackets.
(I just tried the same on some other sheets, and there it gives me 'TRUE' for the same command.)susannewalter The entry 1.7.21 isn't recognised as a date on your system, but there are several ways to transform them into real dates (i.e. sequential numbers) that you then can format to your liking and drag down as you want it.
You din't by any chance include the quotation marks when you enter the formula? Or perhaps the "Show formulas" button on the Formulas ribbon is pressed in the sheet where the formula =ISNUMBER(A6) is shown and not the result.
Perhaps you can upload your file or at least a screenshot.
