help with excel spreadsheet

RevTimms

I am using an excel spreadsheet to enter some data from a bank statement.
I generally have about 20 on a particular day in the month. eg 1.6.16.
If I do a fill down, then the next date on the line is 1.6.17 and so on.
How do I freeze the date, so that they all say 1.6.16 when I fill down.
Many thanks

stinky_daddy

Hi

the obvious way would be to change the way the date is entered, so instead of 1.6.17 etc you would use 1st June 2017 (entered as 1/6/17 making sure that the range of cells is formatted to date of course) and then 2nd June 2017 (entered as 2/6/17), then hightlight those two cells and get the cursor on the bottom right of the lower cell (it should change to a cross) and then drag it down.

HTH

s_d

Undervalued

One way would be to have the date cells as text and not formatted as date. However that would stop you doing any calculations based on the date. If that doesn't matter to you then it is an easy fix.

Gravedigger

Bung in the date then hold Ctrl while dragging down for the number of required cells.

Cornucopia

RevTimms wrote: »

I am using an excel spreadsheet to enter some data from a bank statement.
I generally have about 20 on a particular day in the month. eg 1.6.16.
If I do a fill down, then the next date on the line is 1.6.17 and so on.
How do I freeze the date, so that they all say 1.6.16 when I fill down.
Many thanks

Is there a particular reason to use fill down?

If you just cut & paste, Excel does not change the value. You can cut/copy the value from the original cell, and paste it into any rectangular group of cells you like.

Preferably, the date would be part of the data that you are importing, though.

RevTimms

Many thanks for all of these replies. They are all very useful.
The more I read about excel and the more questions I have, the more I realise how much there is to know.
4 replies. 4 different solutions to the same question!

EdwardB

RevTimms wrote: »

I am using an excel spreadsheet to enter some data from a bank statement.
I generally have about 20 on a particular day in the month. eg 1.6.16.
If I do a fill down, then the next date on the line is 1.6.17 and so on.
How do I freeze the date, so that they all say 1.6.16 when I fill down.
Many thanks

Don't use fill down, just copy the original cell. highlight 20 cells and paste to those cells.

System

Hi

Ctrl + D will copy the cell above.

Fill data down [Ctrl] D
or to the right [Ctrl ] R

mandySaver12

Copy and paste won't change the date

Heedtheadvice

and yet another solution.....

Assume the first date (your first June 16) is in cell A2. In cell A3 enter "=" and click on cell A2 and then hit enter. Copy cell A3 and paste as far down the column A as you wish say 300 rows to cover a months transaction. Each cell will display the date from A2 (1st June).
When you get to the row to enter the second of June transactions (lets say row 22) just enter that day's date (second June). That cell (A22 in this example) will then be correct and subsequent cells will also be second June -until you need to enter the next day where you repeat the update process.

Many ways to do what you want as you realise. No big advantage with this method over copying single cell and pasting in desired cells except done as one action.

As mentioned if you enter as a date (01/06/2016) then that is stored in Excel as a numeric representation of that date and it can then be used in calculations etc (but still displayed as a readable date, formatted to your choice. Can easiest be done by highlighting the column and then setting display format.