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!!!!!
Configuration: Windows 7 Internet Explorer 8.0
A1 is having an entry 1/1/09. now in B1 enter this formula
|
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.
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
|
Change cell format and value to date excel Comparing data in excel spreadsheets Comparing duplicates in two excel spreadsheets Excel 2003 extract data from spreadsheet Excel spreadsheet to sim card Formula for finding date and month till datein excel to How do i highlight different changed values in excel spreadsheet 2007 How to import excel spreadsheet data into sql server database using asp.net c# How to merge 2 excel spreadsheets Insert year excel spreadsheet Join cells date excel Keep data without updating excel spreadsheet Match data excel spreadsheet Mcafee 2009 updates dat file Ms access use macro to import data from excel spreadsheet Norton 360 v3 update database Put y if date is before current date excel Transform a date into month and year