DIGITAL EDUCATION SUPPLEMENT ISSN 2517-1550
How to change lots of dates at once in Excel
I pride myself on always meeting deadlines for my writing work, so I was mortified when I received an email from a client asking, “How’s today’s article going, Terry?”.
As it happens, I was working on the article when the email came through — but I thought I was more ahead of the game. What had happened was that I’d interpreted the list of deadlines I was given as my deadlines. In fact, they were the deadlines the client had set for themselves, ie the dates on which they intended the article to go live.
I managed to get the article to them in time, but that wasn’t the point. I needed to change all of the deadlines so that my deadlines were a couple of days earlier than their ones. I worked out how to change all the dates in one go rather than one at a time. Thank goodness: there were quite a lot of them!
If you are ever faced with a similar conundrum, here’s how to deal with it:
1. If the column containing the dates is not the last column with data in it, insert a column to the right of it.
2. Let's the say the dates column is F. In the empty column to the right of it, enter the formula =F1-2, and copy that down. The '2' is the number of days. What this formula does is bring the date forward by 2 days. Eg, if the date was July 6th 2017, the new date will be July 4th 2017. Obviously, insert a different number if you prefer. For instance, 7 will bring the date forward by one week.
3. Next, select the new column and use the Copy command.
4. Then, with the column still selected, right-click and choose Paste-Values only. (I'm using Excel on a PC so the instructions for you might be different, but it's the Paste-values command you want.)
5. Delete the original column of dates.
You may also be interested in this set of utilities:
For the full list of articles featured in our 2017 Retrospective, please visit: