Creating dynamic worksheets for timesheet tracking

[ambarishsn]

Hello,


I am new to excel and this group. Can one of you please help me in improving my present situation?


I create monthly timesheets for my team members. capture it for each task on a daily basis, submit it on a weekly basis. It would be one worksheet per month.I spend almost 4-5 hrs beginning of each month to come up with a worksheet for that month. I was thinking if there are ways to simplify what I do.


My excel file has,


First column - Resource Name

Second column - Task / Activity

From the third column onwards, each column would be for a single workday (highlighting saturday and sunday so that timesheets are not captured for these days).


Resource name is on one row and tasks line up in rows below the resource name row. I capture leaves any day on a seperate row (last row for the resource).


I do resource level grouping in this worksheet (for work hours and leave hours) and also have another worksheet where i do some summarization of values from this worksheet. It is basically for generating resource utilization and resource availability metrics.


Now what I am thinking of doing is,

1. have a worksheet as master. There will be two cells (maybe drop down cells). One for month and other for year. Say I select, Year = 2012 and month = September. I should have a new worksheet added as September2012 and it should have the monthly calender as i have now, be generated. I will have team's resources listed in a seperate master page. When I generate a timesheet for a month, it should pick the resources from the master list and create a worksheet as I have said above.


Wanted to know if this is achieavable. If so, can you give me thoughts on how to do it. I would love to try it doing myself.


Best regards,

Ambarish S N

 

[Luke M]

Certainly achievable!

The two parts I see to this are setting up the calendar, and then setting up the resources.

If you're fairly new to XL, I'd recommend first just recording a macro of yourself going through the steps to create the calendar and pasting in the resources. This will give a clunky, albeit semi-function block of code. The next step from there is automating the code to make it more versatile for different months (aka, inputs) and removing excess code (like scrolling the window up and down 50 times).

As the looping through dates might be a little tricky, here's some code to get you started. It should setup the dates and hightlight weekends.

[pre]

Sub CreateDates()
'Define the variables we want to use
Dim MyMonth As String
Dim MyYear As Integer
Dim xDate As Date

'Assign values to the variables we know about
MyMonth = Worksheets("Sheet3").Range("a1").Value
MyYear = Worksheets("Sheet3").Range("A2").Value

'Create the starting date
xDate = DateValue("1 " & MyMonth & " " & MyYear)

'Place the first date in 3rd column
i = 3

'turn off the screen updating so things run faster
Application.ScreenUpdating = False

'Create a new worksheet
ThisWorkbook.Worksheets.Add

'Might want to give new sheet a name
'possible idea:
'ActiveSheet.Name = MyMonth & MyYear

Do
Cells(1, i).Value = xDate 'Put the date into row 1, starting in col C

If Weekday(xDate, vbMonday) > 5 Then 'It's a weekday!
'Highlight columns in yellow
Cells(1, i).EntireColumn.Interior.ColorIndex = 6
End If
xDate = xDate + 1 'Increment our date and column counter
i = i + 1

Loop Until Day(xDate) = 1 'Exit the loop once we get to the 1st of next month

'Paste in resources
'I'll let you take it from here

'make sure to turn this back on!
Application.ScreenUpdating = True
End Sub

[/pre]

 

[braden]

Hi Ambirish, I think an excel timesheet is quite cumbersome and outdated. You should definitely switch to Replicon's hassle free employee time tracking solution. It is cloud based as such can be accessed from anywhere and has got simple to use features.

 

[braden]

Hi Ambirish, I think an excel timesheet is quite cumbersome and outdated. You should definitely switch to Replicon's hassle free employee time tracking solution. It is cloud based as such can be accessed from anywhere and has got simple to use features.

 

[ambarishsn]

Thank you Luke M for the response. Attempting on how macros work. Will go ahead and try this out.