Excel - Increasing Date Help


Recommended Posts

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.

Link to post
Share on other sites
+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.

 

image.png.3110ed974001cd550dfd94e3a148af95.png

 

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

  • Like 1
Link to post
Share on other sites
Sir Topham Hatt

Hmm, this may be the only way :(

Link to post
Share on other sites
+spikey_richie

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

 

image.png.f506b2b96a0c31ce1e4cbf17aa542ed5.png

  • Like 1
Link to post
Share on other sites
  • 1 month later...
Sir Topham Hatt
Posted (edited)

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:

 

001.JPG.0e37084801a8dd92a530606dd781e116.JPG

 

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:

 

002.JPG.b1cbd07d7bfb57ecc33e1c750d76226c.JPG

 

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 by Sir Topham Hatt
Link to post
Share on other sites
Brandon H

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

 

image.png.3486d969eaed4f02f86bf027a2b592fb.png

 

 

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

 

image.png.4ec16574e787806c39771d5532b834b9.png

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.