Time Cards - Excel Vs Access as option

[Aussiebear]

Given some of the comments offeredin other threads, could someone please advise me as to whether I'm better off developing something in either Excel or Access, or a combination of both, if I want to enter and record time card information.

Currently the company records both the time worked per employee and then breaks down the hours worked into hours worked per unit function in an effort to determine where the labour costs are being incurred.

With the example atached, am I better off to try and build the "time card" as a User form or simply attach command buttons at the bottom of the card layout on sheet 1?

If you recommend just Excel, how does the information become stored and will I retrieve the information under Pivot tables/ Pivot Charts?

If you recommend just Access, can Access allow a "time card' layout like this for data entry?

If you recommend a combination of Excel and Access, well I might as well take up drinking full time!! (Did I mention, that Access and I have a dislike for each other?)

[Bob Phillips]

This is how I would do it.

First I still wouldn't bother with Access.

Second, I would create a template timesheet, and add worbook sheet change event to handle time input of format 3.5.

Third, I would create a master data workbook with three sheets, the first with the employee names, other data you want about them, and a second for the timesheet data, and a third for a pivot analysis.

Fourth I would create an addin that created its own custom menu with the following functions,
- create a new workbook with a timesheet worksheet for each current employee
- consolidate the timesheet data into the master data workbook
- view the pivot table
- any other niceties you may want.

I wouldn't use Access as I feel that there is a ton of native Excel functionality that you could tap in this application. I m ight consider using Access as purely a database (none of its form functionality) in place of the master data workbook, using ADO to update it, and generate the pivot table dynamically from that, but probably not. The data wouldn't warrant it.

[Aussiebear]

OKay, 2 workbooks, 1 for timesheets per employee, and a second being the Master workbook with the consolidated time sheet data ( and pivot table/ Pivot Chart functions attached).

[Bob Phillips]

3 - don't forget the XLA.

Who completes the timesheets, the employee, or a central admin? If the former, you should create a separate workbook per employee and put in a central server directory, so the employee can update, then one of the XLA functions would be to consolidate them all. This process should also validate that the total jobs time equals the daily worked time.

BTW, the start date should be a function of the timesheet generator function, an inpout to that function.

[mdmackillop]

Hi Ted.
Unless you need the break times, why not just input this data in minutes.

[OBP]

Access everytime, it does naturally what you are forced to use Excel VBA to do the same and usually not so well.
You could reproduce that timecard, but it is not necessarily the best method of data input.
It may be good for outputting the data though. If you decide the Access route I am quite willing to either develop it for you or with you.

[Aussiebear]

Just when I thought it was safe to go in the water.....

OBP, thank you for your offer, but I'm simply awful at Access and only just slightly better at Excel.

Just how difficult would it be to do something in Access?

Ted

[Aussiebear]

Malcolm, I need the break involved as the Company is quite perdantic re the hours worked. I'm off to build the Template or something like it.

[Aussiebear]

3 - don't forget the XLA.

Who completes the timesheets, the employee, or a central admin? If the former, you should create a separate workbook per employee and put in a central server directory, so the employee can update, then one of the XLA functions would be to consolidate them all. This process should also validate that the total jobs time equals the daily worked time.

BTW, the start date should be a function of the timesheet generator function, an inpout to that function.

Bob, A Farm Supervisor will be the sole data entry/ manulipulator of the data, in a Central Admin area. He gets access to the Time cards as completed by the employees and enters the data from there.

Thought about the validation of the Hours worked per day against the Functions hours, but didn't know how to do this.

The date function is firstly based on when the time cards start, (for us its a Saturday). The Supervisor doesn't always get to enter in the data, the week following the time card is completed, hence the need to enter in a date in the A2 cell

[Aussiebear]

This is what I was given, to work from.

[Bob Phillips]

Is that the existing time card Ted? If so, how does it relate to the previous one you showed us (BTW this one is truly awful, that previous one is a much better time card).

I have started kniovking up an app, about halfway through, but this time card is (may be?) a curve ball.

[Bob Phillips]

PS forget Access for now. YOu will have enough problems getting it to work in Excel, it could always migrate later (although I am sure you will never justify the effort).

BTW is this going to get you kudos at work?

[mdmackillop]

PS forget Access for now.

Agreed.

[Aussiebear]

Is that the existing time card Ted? If so, how does it relate to the previous one you showed us (BTW this one is truly awful, that previous one is a much better time card).

I have started kniovking up an app, about halfway through, but this time card is (may be?) a curve ball.

Bob, This is what my Supervisor set up, and he simply saves each sheet at the end of the week and renames a copy of the "Blank" sheet. Whilst playing golf today the Feedlot Financial Controller indicated to me that he and the Supervisor had been discussing a method to allocate costs better. In the past they have been simply guessing a ball park figure.

Ted

[Aussiebear]

Have struggled on with a new approach, but am running into trouble with the data entry of time. It would be so much more effecient if it were possible to simply use the numeric keyboard to enter times rather than having to use the Shift + Colon keystrokes.

I had found some code on the net which said it was the way to go but something appears wrong.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal As Integer
If Target.cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("H3:M368") Is Nothing Then Exit Sub
With Target
   vVal= Format(.Value, "0000")
   If IsNumeric(vVal) And Len(vVal) = 4 Then
      Application.EnableEvents = False
     .Value = Left(vVal, 2) & ":" & Right(vVal, 2)
     .NumberFormat = "[h]:mm"
   End If
End With
Application.EnableEvents = True
End Sub

I placed this in the Sheet in which I wanted the code to run, and when I enter a time such as 07.00 for 7am.... the value in the cell reflects both a date and a weird time.

[OBP]

You don't have that problem with my Access version because access inputs it as plain numbers and puts the colon in for you.

[Aussiebear]

ROFL.... got me

[OBP]

You are up late, I have emailed you a copy the latest version.

[Aussiebear]

Yep, got it.... and its .... different.

[OBP]

Sorry progress is so slow, I am juggling 4 databases at the moment as well as time on here. I will let you know when I think it is getting nearer to completion.