A few words of thanks would be greatly appreciated.

How to Update Dates in Excel Spreadsheets

Do you use Excel spreadsheets to keep track of your weekly, monthly or annual expenses but find yourself updating all relevant date and content information manually? There is an easier, more effective solution and in this article we will provide you with a macro formula which will help with the automatic update of information.

Macro to Update Dates in Excel Spreadsheet

The macro will bring up an input box. there type the year for e.g. 2020 or 2021. 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

Should this macro not provide the solution to your problem, this article contains multiple macro solutions which might be of help.

Image © motortionfilms - 123rf.com

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 Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « How to Update Dates in Excel Spreadsheets », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!