Excel & Date Help

MarkeR

MarkeR

MarkeR

Soldato
Joined
15 Aug 2011
Posts
4,981
Hi Guys

I have a 3 month rota to do, I use excel to format it the way I like it! What I would like to do on a row is have

25th Aug 26th Aug etc etc

But instead of having me go through every cell, is it possible that the following cells after I fill in the first one can auto fill with the correct date?

Thanks
 
Type in 25-Aug and Excel will recognise it as a date. If you then drag the cell down (or across) it should fill the dates consecutively.

My guess is that it wasn't playing nice for you because you used "th" e.g. "25th", Excel doesn't play nice with that for some reason.

EDIT another way is just =A1 + 1 that will add 1 day to the date in A1
 
Custom formatting can also be used here, select the range of cells you want to format, right click and select properties. On the numbers tab select 'custom' at the bottom of the list.

Enter dd-mmm into the text box on the right and you'll get the format right.

As mentioned above, Excel does not like ordinal notation and can subsequently treat a date entered this way as a string. Never store dates as strings, this is bad. Dates in Excel are taken as the number of days from 00/01/1900 (assuming use of the Gregorian calendar).
 
Just to touch on Grrrrr's post, if you enter the date as suggested or as 25-08 it will auto correct to 25-Aug and you then want to click on and drag the bottom right hand corner of the cell, you'll see that only one of the corners has a little box on it.
I much prefer to right click and drag as it gives you a context menu which left click and drag doesn't offer. It should offer you the option of Fill Series.
 
You must log in or register to reply here.
Back
Top Bottom