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.:thumb

[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. http://vbaexpress.com/forum/images/icons/icon10.gif

[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.

[Aussiebear]

That's okay OBP, my supervisor said he wanted to "play" with what I had at this stage and in one on my most discerning looks and said " one does not play with the cake while its still in the oven" and walked off.

That'll put him off for a couple of weeks.... but after that I'm a shot duck.

[Aussiebear]

Is this a " correct" (permissible) section of code?


Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String

Application.EnableEvents = False
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
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End With
End IF
If Intersect(Target, Range("C3:F368")) Is Nothing Then Exit Sub

With target
vVal = Format(.Vale, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm AM/PM"
End With
End If
Application.EnableEvents = True
End Sub



I have one range to be formatted as [h]:mm and another to be formatted as [h]:mm AM/PM. XLD suggested testing in the same Worksheet_Change event, so am I on the right track here?

Ted

[Bob Phillips]

No, you would never get to the second action


Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H3:M368")) Is Nothing Then
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With
ElseIf Not Intersect(Target, Range("C3:F368")) Is Nothing Then
With Target
vVal = Format(.Vale, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm AM/PM"
End If
End With
End If
Application.EnableEvents = True
End Sub

[Aussiebear]

Thanks Bob. Its a jungle out there for us beginners

[Aussiebear]

Getting a compile error "End With without with" at the first End With. It seems matched, so what causes this?







Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H3:M368")) Is Nothing Then
With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End With
End If
ElseIf Not Intersect(Target, Range("C3:F368")) Is Nothing Then
With Target
vVal = Format(.Vale, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm AM/PM"
End With
End If
End If
Application.EnableEvents = True
End Sub

[Aussiebear]

Should the End With and End IF be reversed after the line .numberformat = "[h]:mm"

[Bob Phillips]

Sorry, I just coipied your code and that error was already in there.



Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String

Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("H3:M368")) Is Nothing Then

With Target
vVal = Format(.Value, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm"
End If
End With

ElseIf Not Intersect(Target, Range("C3:F368")) Is Nothing Then

With Target
vVal = Format(.Vale, "0000")
If IsNumeric(vVal) And Len(vVal) = 4 Then
.Value = Left(vVal, 2) & ":" & Right(vVal, 2)
.NumberFormat = "[h]:mm AM/PM"
End If
End With

End If

Application.EnableEvents = True
End Sub

[Aussiebear]

Fixed the code, now it doesn't seem to matter what I do as it gives date and time values.

Anyone with a guess as to what is now going down the plug hole?

Ted

[Bob Phillips]

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim vVal As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("H3:M368,T3:X368,AF3:AJ368")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = (.Value \ 100 + (.Value - (.Value \ 100) * 100) / 60) / 24
.NumberFormat = "[h]:mm"
End If
End With
ElseIf Not Intersect(Target, Range("C3:F368,O3:R368,AA3:AD368")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = (.Value \ 100 + (.Value - (.Value \ 100) * 100) / 60) / 24
.NumberFormat = "h:mm AM/PM"
End If
End With
End If
Application.EnableEvents = True
End Sub

[Aussiebear]

So, no formatting of the cells is required as this code now completes this task for the data entered?

And.... am I still meant to be able to enter the values as "0000" from the numerical keyboard?

At the moment 0700 for 7am results in 16800:00

[mdmackillop]

Hi Ted,
I had a little time to play with this, so here's a userform interface. I've changed time format to 24 hour as this makes life simpler for data entry, but with a bit more work it can be changed if the approach is of any use.
To use, either select a date and click the button. or enter a date on the form, then select the Worker from the dropdown. Additional names can be added with minimal changes to the code.
Regards
Malcolm

[Aussiebear]

Thank you Malcolm. I shall have a play around tomorrow night after work. Right now its 20 to Midnight and I need to be up again at 4.

Ted

[Bob Phillips]

So, no formatting of the cells is required as this code now completes this task for the data entered?

And.... am I still meant to be able to enter the values as "0000" from the numerical keyboard?

At the moment 0700 for 7am results in 16800:00

Just enter it as 700, 714, 729, etc.

I can't get 16800 as you do.

[Aussiebear]

Yes I imagine its because I've had formatting in place on this sheet before your contribution of the code. Its possible that the code format and the sheet format are clashing.

Ted

[Aussiebear]

MD, If an employee should leave during the year, and be replaced or a additional employee be added, do I make the necessary changes to the sheet, then go to Private Sub refresh() and add an addition Case "New Name" and WPos = 36?

Ted

[Aussiebear]

MD, I imagine that the Supervisor will be sitting there with a time card in hand, entering data. Once he enters a days "values" and it writes to sheet, how does the form clear to allow the next day's values to be entered for the same employee?

On some days, they may actually do the same times ( and work functions) as the day before, is it possible just to change the date, tab through to Write to the sheet and press enter?

If I try to select the employee name by typing their name rather than selecting from the drop down list, it boots the form to a new position which requires the form to be dragged up the sheet for it to be filled in. Can this be fixed? Perhaps to show the form just below the last row of data for the employee selected, although this may not be good if there's a significant break between working days.

Something that has just occured to me is, we have a policy of allocating a min 8 hours for a sick day, or for a day which is part of approved holidays. I will need to show the hours in the employee time as 0700, 1230,1300,1530 and zero hours in the work functions. Will this be allowed in the form given that it will show as 8 hours unallocated?

If I create an error it boots me out of the workbook, can this be changed so that it simply clears the data from the form and gives an error message?

Otherwise its very impressive. Thank you

Ted

[mdmackillop]

Hi Ted,
As usual, we can do most things with the form. It's a question of identifying your needs. It sounds like you need additional columns for your time allocation, or some other way to record sick time/holidays.
Regarding the form position. If you can set Top and Left values for the form which suit your screen, I can code these in. The form can move to the row relevant to the date/worker being processed, as soon as this data is entered.
Error handling can be added as required.
Regards
Malcolm

[Aussiebear]

Malcolm, I notice that when you input the values in the work times section it is already written into the sheet, and by the time you get to the "Write to Sheet"button all values are already showing in their respective cells.

So what does the button actually do?

Can you change this so that the employee name remains the same but the date is advanced one day and the rest of the data is cleared to the default values.

Lets leave the issue of holiday/sickdays for now. Its still a cost incurred but since it cannot be allocated to a work function, then its an "extra"

Ted

[mdmackillop]

Hi Ted,
The userform should be regarded as a trial version, as I didn't know if this was the way you wanted to go.
Not all entry changes will update the sheet and the button forces an update.
We can certainly add a next day button for the same worker.
Why not add an "Other/Extra" column(s) to your Crops which would allow full allocation of time. We could even attach a comment to this field (if used) I think you did this elsewhere.
To save modifying code, the ListtIndex of the combo could be used to set the necessary offsets, but it depends upon correct arrangement of Names and Spreadsheet. Would this be desireable or a problem?

[Aussiebear]

Desireable... most certainly.

[mdmackillop]

Hi Ted,
Here's an update with some added bits and pieces. I've not added error handling as yet. Let me know where your problems occur.
Regards
Malcolm

[Aussiebear]

MD, its getting closer to being perfect with every move you make.

I notice that when you enter data via the form it moves the display (top line of spreadsheet) to the next date for data to be entered, but it also boots you to the right. If you wanted to keep entering a weeks time data for Billy W is it possible to retain Billy's section so that you can review the data just entered to ensure its correct, then when you change to another employee it shows just their data as recorded?

[mdmackillop]

Hi Ted,
The data entry should be working as you wanted. Can you step through the SetView sub to see if there is a problem there?. I'm having difficulty moving focus to the command button when time allocations are complete, not a real problem, but a neater use. I've commented out this line meantime.
I also added some instructions for "others"
Regards
MD

[Aussiebear]

Can't replicate the error tonight.... must be an operator stuff up. ( well I'll admit it, it normally is where I'm involved)

Its also good to see the VBAExpress logo and hyperlink on the form. I'd still like to see where possible credit being given to those who have helped here..after all it'd still be just a rough concept loosely banging around in my simple mind.

[Aussiebear]

MD, What is Module 3 supposed to be doing? It has more ActiveWindow.Scrollrows than fans at a soccer match.

Ted

[mdmackillop]

Hi Ted,
Modules 2 and 3 can be deleted. Module 3 looks like I forgot to turn off my macro recorder (Yes, I still use it when making up code!)
Regards
Malcolm

[Aussiebear]

Rofl.... and here I was thinking it was some new and un explained fancy code to cook chickens ( Scotland style).