Excel - Increasing Date Help

[Sir Topham Hatt]

I have a speadsheet with a weeks worth of dates in each row.

Is there a formula that I can use in the first one so the 6 below will automatically increase their day by 1?

 

So if I put 01.04.21 in the first box, all the others will automatically change to 02.04.21, 03.04.21, 04.04.21...  etc ?

 

I have tried a few but it doesn't seem to work and even highlighting the first date, then dragging down and using the little autofill square (that appears next to the cell) doesn't have a "date" option.

[spikey_richie]

If you express the dates with a / delimiter, the autofill works as you'd expect. It's always handy to start with 2 values as well, select both and THEN drag down. Excel then knows the pattern to follow.

 

 

Maybe you could use /, then replace it with . afterwards?

[Sir Topham Hatt]

Hmm, this may be the only way

[spikey_richie]

@Sir Topham Hatt An alternative would be to put each date part into a separate column, then concatenate and drag down.

 

[Sir Topham Hatt]

This is where I lose faith in how "helpful" computers can be.

 

I want the date format to be:  01.01.10

 

But Excel doesn't seem to recognise this as a date format.  The closest would be: 1.1.10.

 

So on one sheet, the cell seems to know it's a date as in the box at the top of the sheet, the date is written as 01/01/2010 but on the sheet, it's translated to 1.1.10.

I've dragged it down and it auto-filled:

 

 

The next sheet however, even though the format of the cell is "date" - the same as the sheet before, it won't format the date as a date and won't auto fill:

 

 

I really don't understand it.

 

Right, so if I type the date format: 01/01/2010 into the top box, then it reformats it with . in the boxes and I can then drag down.

Shame you can't add the former 0's though.

 

I think what frustrates me is the 15 minutes I've spent trying to make things a little easier, I could have spent manually filling in the dates.

Edited May 3, 2021 by Sir Topham Hatt

[Brandon H Supervisor]

what version of office do you have? I have Office 365 here at work and there are 2 date format options that have the proceeding 0 on my format list

 

 

 

edit: actually I see the issue, there is FAR fewer selections when you set the locale to UK, try changing it to US to see if you get the option you want