A few words of thanks would be greatly appreciated.

Update dates in excel spreadsheet each year


I have 4 spreadsheets linked together which I use to keep track of my accounts.

All 4 spreadsheets are in one folder.

On each spreadsheet I have the date Monday to Sunday with a weekly total underneath for each month running down column A.

My dilemma is that each year I have to copy all the folder, wipe all the data leaving the formulas in, and delete all the dates and change to next year as obviously each year the 1st will not land on the same day therefore I have to go through the long process of deleting all and re typing in the days, dates for each week, add a row for total at end of week and carry on like that so each page is a different month and the weekly totals run all the way through the year wk1, wk2, wk3, wk4, wk5, wk6 etc.

Does anyone know of an easier way of excel knowing what day the new year begins and inserts it into a new spreadsheet?


See whether this macro will help you. The macro will bring up an input box. there type the year for e.g. 2010 or 2011. (I assume you wont extend to the end of the century).you can format date column A as you like.

Test the macro and decide.
Sub test()
Dim y As Integer, d As Integer
y = InputBox("type the year, e.g. 2010")
If y Mod 4 = 0 Then
d = 366
d = 365
End If
Range("a1") = "1/1/" & y
Range(Range("A1"), Cells(d, "A")).DataSeries Rowcol:=xlColumns, _
Type:=xlChronological, Date:= _
        xlDay, Step:=1, Trend:=False
Range("B1").Formula = "=weekday(A1)"
Range("B1").AutoFill Range(Range("B1"), Cells(d, "B"))
End Sub


Thanks to venkat1926 for this tip on the forum.

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Published by . Latest update on by aakai1056.

This document, titled "Update dates in excel spreadsheet each year," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).