PROBLEM OF THE FORTNIGHT
Can I change Excel dates with a formula?
Q I have a rolling programme of Bible studies covering three years. I’ve been altering the dates in the Year 1 section before the expiration of Year 3. I then need to alter the dates in the Year 2 and Year 3 sections. I’ve been doing this laboriously for some years. I wonder if there is a way of changing dates with an Excel formula, and then printing them off? I just want to make my job a little easier. Thanks for an excellent, informative and easy-to-read magazine.
Tony Fowler
A What you want to achieve is perfectly possible. However, because of the nature of your spreadsheet, the solution isn’t all that easy to explain or visualise. So it took us a while to formulate our response.
For reasons we don’t wholly understand, currently you have the day, date, month and year for each session split over four different columns. We appreciate why you’ve separated the day, because that fits the way you want to present the schedule.
However, splitting the date, month and year into three adjacent columns seems unnecessary – and would complicate the solution.
Perhaps you’ve done this because you were unaware that dates contained in a single cell can be formatted the way you want? Or perhaps because you’ve been relying on Excel’s search-and-replace function to accelerate your annual updates a little? Regardless, those entries are in columns G, H and I.
For the sake of a comparative example, we’ve consolidated all three as a single date in column J on your original spreadsheet. To do this, just type a date into cell J5 – 17/3/2023, for example, since your weekly schedule seems to start on a Friday in mid-March – and then format that cell to display the date in full.
To do that, right-click J5 and choose Format Cells (see screenshot ). Then, in the
