| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Timesheet
I am making a timesheet, and for each month I need a separate sheet with the date running down column A. So in the first sheet (Called January) A2- A32 will be 1/1/06 - 31/1/06. When I copy this sheet and rename it February, I would like it to show the dates for this month automatically, any way of doing this? Thanks all! -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. This assumes that the year of interest is entered in cell B1, A1 is available for the month name, and A2:A32 is formatted for dates. Change the sheet tab name, select a cell on the sheet, and then slect another cell to fire the event. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myYear As Integer Dim myMonth As Integer Dim myDay As Integer If Range("$A$1").Value = ActiveSheet.Name Then Exit Sub With Application .EnableEvents = False .ScreenUpdating = False End With Range("$A$1").Value = ActiveSheet.Name Range("A2:A32").ClearContents myYear = Range("B1").Value myMonth = Month(DateValue(ActiveSheet.Name & " 1, " & myYear)) myDay = Day(DateSerial(myYear, myMonth + 1, 0)) Range("A2:A" & myDay + 1).FormulaR1C1 = _ "=DATEVALUE(R1C1& "" "" & Row() - 1 & "", " & myYear & """)" Range("A2:A" & myDAys + 1).Value = Range("A2:A" & myDAys + 1).Value With Application .EnableEvents = True .ScreenUpdating = True End With End Sub "ChrisMattock" wrote in message news:ChrisMattock.2ais91_1152182703.8544@excelforu m-nospam.com... I am making a timesheet, and for each month I need a separate sheet with the date running down column A. So in the first sheet (Called January) A2- A32 will be 1/1/06 - 31/1/06. When I copy this sheet and rename it February, I would like it to show the dates for this month automatically, any way of doing this? Thanks all! -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
Well it adjusts for the Month, i.e. February had 28 days in it, but each day is being displayed with an error, the top date being #VALUE! any suggestions? -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
Did you put a valid year number in cell B1?
-- HTH, Bernie MS Excel MVP "ChrisMattock" wrote in message news:ChrisMattock.2aiyyq_1152191415.9068@excelforu m-nospam.com... Well it adjusts for the Month, i.e. February had 28 days in it, but each day is being displayed with an error, the top date being #VALUE! any suggestions? -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
Yes I have done... =DATEVALUE($A$1& " " & ROW() - 1 & ", 2006") is the result that shows in the formula bar, but and error appears in the cell. -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
Chris,
It might be a regional date setting... Does =DATEVALUE("January 1, 2006") produce an error? If so, what are valid strings that DATEVALUE will work with on your computer? HTH, Bernie MS Excel MVP "ChrisMattock" wrote in message news:ChrisMattock.2aj0cp_1152193234.9335@excelforu m-nospam.com... Yes I have done... =DATEVALUE($A$1& " " & ROW() - 1 & ", 2006") is the result that shows in the formula bar, but and error appears in the cell. -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
No that doesn't work, here are some that do... (thanks a lot for all this) =DATEVALUE("8/22/2008") =DATEVALUE("22-AUG-2008") =DATEVALUE("2008/02/23") =DATEVALUE("5-JUL") -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
Chris,
Try changing Range("A2:A" & myDay + 1).FormulaR1C1 = _ "=DATEVALUE(R1C1& "" "" & Row() - 1 & "", " & myYear & """)" to Range("A2:A" & myDay + 1).FormulaR1C1 = _ "=DATEVALUE(Row() - 1 & "" "" & R1C1 & "" " & myYear & """)" HTH, Bernie MS Excel MVP "ChrisMattock" wrote in message news:ChrisMattock.2aj1z1_1152195304.7639@excelforu m-nospam.com... No that doesn't work, here are some that do... (thanks a lot for all this) =DATEVALUE("8/22/2008") =DATEVALUE("22-AUG-2008") =DATEVALUE("2008/02/23") =DATEVALUE("5-JUL") -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
OR (since I forgot the dashes....)
Range("A2:A" & myDay + 1).FormulaR1C1 = _ "=DATEVALUE(Row() - 1 & ""-"" & R1C1 & ""-" & myYear & """)" HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Chris, Try changing Range("A2:A" & myDay + 1).FormulaR1C1 = _ "=DATEVALUE(R1C1& "" "" & Row() - 1 & "", " & myYear & """)" to Range("A2:A" & myDay + 1).FormulaR1C1 = _ "=DATEVALUE(Row() - 1 & "" "" & R1C1 & "" " & myYear & """)" HTH, Bernie MS Excel MVP "ChrisMattock" wrote in message news:ChrisMattock.2aj1z1_1152195304.7639@excelforu m-nospam.com... No that doesn't work, here are some that do... (thanks a lot for all this) =DATEVALUE("8/22/2008") =DATEVALUE("22-AUG-2008") =DATEVALUE("2008/02/23") =DATEVALUE("5-JUL") -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
That's awesome, thanks! -- ChrisMattock ------------------------------------------------------------------------ ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912 View this thread: http://www.excelforum.com/showthread...hreadid=558817 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Timesheet
You're welcome...
Bernie MS Excel MVP That's awesome, thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| displaying selected items from a yearplanner in a timesheet | Excel Discussion (Misc queries) | |||
| How do I calculate an employee timesheet in Excel? | Excel Worksheet Functions | |||
| Timesheet help | Excel Worksheet Functions | |||
| overtime on timesheet | Excel Worksheet Functions | |||
| timesheet with running total of overtime | Excel Worksheet Functions | |||
Linear Mode
