Update dates in excel spreadsheet each year [Solved/Closed]

Report
-
 oclaf -
Hello,
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 mon to sun with a weekly total underneath for each month running down column A.

My dilema is that each year i have to copy all the floder, 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?????

Please please help!!!!!

If i can help it i wuld prefer to carry on using excel for my accounts

HELP ME!!!!!

3 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
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.

your second question is not clear to me.

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
Else
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
3
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
A1 is having an entry 1/1/09. now in B1 enter this formula
=WEEKDAY(A1)
you will get 5
the weekday starts from Sunday in this formula
1 Sundnay, 2 Monday, 3 Tuesday, 4 Wednesday , 5 Thursday, 6 Friday and 7 Saturday.

if you want to check enter in some cell e.g. A5 today's date (11/8/09)
and in the next cell to the right in B5 type
=WEEKDAY(A5)
you will get 1 which is Sunday

Will this information be useful to you.
thanx 4 that. ive tried it and i dont think it helps me with what im trying to do.

its difficult to explain but in column a i have the dates and a total for that week, then the next dates and a total for that week.

i need to enter the dates for 2010 and copy as a template. i will use the 2010 untill end and then in 2011 i will bring up the template but i then need to change all the dates to show 2011.obviously in different years weeks and months dont run the same so it all needs changed.

the other problem i have is that on one of the spreadsheets which is linked up to the others it needs to have the days in column b relating to the dates so i need to change the days so they match up with the dates aswell.

im sorry if this is difficult to understand, i dont think im explaining very well but im just hoping im getting the message across.

in desperate need of help
i too would like to know how to go from year to year without having to clear all my data from say 2009, then start with 2010 with what is essentially a new spreadsheet...

i used to know how, but not being the PC geek anymore...into other things...but there has to be an easier way? anyone?

Newsletter

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!