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:

Review of ASAP Utilities for Excel