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.
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.
Riny_van_Eekelen , thanks for that, that makes sense to me somehow.
I just tried it again - look what it does.
It seems to recognize certain date formats and drags them across correctly, others not.
I rechecked my regional settings for date/time, and the format is 7/1/2021.
So SOME date formats at least seem to work now.
Do you know how I can change that to another one, say the one I wanted to do initially, 1.7.21, 2.7.21...
I tried through the drop down in Excel under More Number Formats (in the General, Text, Date dropdown), but it did not seem to change anything.
susannewalter Once you have sorted out how the enter the date, for the 1st of July, 2021, you can use Custom formatting to display it like 1.7.21
See attached, row 12
- Do I first select Custom, choose one format in an empty cell, and then enter the date there, or the other way round? Both don't seem to work.
Sorry, I'm still confused how you would get that exact numbering, 1.7.21., 2.7.21