Forum Discussion
Excel fill function only changes the year
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.
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.
Riny_van_Eekelen no I didnt include quotation marks. The formula with TRUE/FALSE seems to work on other files though.
I checked the 'show formulas' as well, it's not on.
I'm attaching the file here.susannewalter First of all, the whole sheet is formatted as Text. Then you probably started entering text, numbers and dates, but everything became text. Excel does recognise the numbers. Dates just look like such but became texts. Also, when you enter a formula in a cell that is formatted as text, the formula becomes a text string.
The one formula that worked in I10 was probably re-entered after you changed the cell format to General.
Set all cells to General first and then re-enter the first date in A9 and make sure to use a date format consistent with your regional settings. Then drag it across and you will note that the days will increment by 1 for every cell.