VBA CODE - Tracking work hours

Question

VBA CODE - Tracking work hours

Kenja824 (Automotive)

(OP)

28 Dec 16 22:58

I am looking to make a module(s) that will help me keep track of hours worked each week. The pic below is the basic idea of my format. Specifically, what I am looking for is what will come up in columns L and M in the pic.

*Each whttp://www.eng-tips.com/threadminder.cfm?pid=766ee... starts on Monday and ends on Sunday.

In column L, it will display the STRAIGHT time hours for each day.

In column M, it will display only the OVERTIME hours for each day. (anything over 40 hours for the week)

In Column K, I already have the formula .... =SUM(IF(E10>D10,E10-D10,"0"))+(IF(G10>F10,G10-F10,"0"))-I10 .... The code for L & M can recalculate the punch in and punch out times, or it can be derived from the totals of column K, whatever is easiest, I will work with.

NOTE: If you can do this with simple formulas that can be copied down the two columns, that would work for me too. I just figured this might be easier to write VBA code for if you know it. I have been struggling to learn it for a while and my understanding of VB and VBA code is coming very slowly. Veeeeerrryyyyy sllooooowwwwlllyyyyy..... lol It may be hindering me that I am trying to learn both at the same time. Not sure.

if this is not too difficult and you can help me with this, it will be greatly appreciated, as not only will it get me what I need, but it will give me more code to read and understand and learn from.

Please ignore the fact one number is red.

Hope everyone had a wonderful Christmas and has a great New Year.

onlyadrafter (Mechanical) · Answer 1 · 2016-12-29 12:21:24

Hello,

If you can add two columns (to make this easier) then you can do the following:

In N10 enter this formula:

=WEEKNUM(C10,2)

In O10 enter this formula:

=SUMIF($N$10:N10,WEEKNUM(C10,2),$K$10:K10)

In L10 enter this formula:

=IF(O10>40,K10-M10,K10)

In M10 enter this formula:

=IF(AND(N9>40,O10>40),K10,IF(O10>40,O10-40,0))

copy these down as far as required.

There is probably a simpler solution, but not the best as time calculations. However, this does give the same results as your post.

You could combine the 'extra' formulas into cols L & M.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

Kenja824 (Automotive) · Answer 2 · 2016-12-29 14:02:34

Hey Onlyadrafter

Thanks for the help. At first these did not work, but I realized you were naming cell C10 in a couple of the formulas and that cell is empty at this time. I assumed you meant A10 to get the day of the week? This seemed to work at first but when I dragged it down, I the STR column (L) would continue to give amounts after it totaled 40 hours for the week, only the amounts returned were a negative of the cell one up in the L column.

I was able to fix this by making the formula in "L10" to be....
=IF(IF(O10>40,K10-M10,K10)<0,"",IF(O10>40,K10-M10,K10))
So that if it became a negative number, it would return as a blank cell.

For M10, I changed the "0" return to "" as well to get my preferred results....
=IF(AND(N9>40,O10>40),K10,IF(O10>40,O10-40,""))

I get the feeling there is an easier and cleaner way. Most likely with a VBA code, but this gets the job done. If in playing with it I find a cleaner way, I will post it.

Thanks for the help.

onlyadrafter (Mechanical) · Answer 3 · 2016-12-29 16:51:55

Hello,

You are correct, I did mean Cell A10, not C10.

Have attached the file I created. I also copied the data down to try and replicate what you found, but it still appears to give correct results.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

http://files.engineering.com/getfile.aspx?folder=3c9bf7fb-96e2-43ff-9a46-48

Kenja824 (Automotive) · Answer 4 · 2016-12-29 17:39:05

Interesting. So I found why I was getting negative numbers. The code in your post above for M10 ... =IF(AND(N9>40,O10>40),K10,IF(O10>40,O10-40,0)) .... has an N9. In the file you loaded for me, the cells below that all have O10, O11 instead of N10 and N11. So when I copied it and pasted it with the N and dragged it down, it gave me a negative number.

The interesting thing is that I found why I need to use the "" to get a blank cell and you dont. I showed the formatting for the same cell for each of our files in the pic below. I just have no idea why its like this and how to fix it. lol

onlyadrafter (Mechanical) · Answer 5 · 2016-12-29 19:19:01

Hello,

Oops. Just spotted that. The formula in M10 should be:

=IF(AND(O9>40,O10>40),K10,IF(O10>40,O10-40,0))

copy this down as far as required.

The reason I have blank cells, is that in the FILE, OPTIONS, ADVANCED. Display Options for this Worksheet, I have unchecked the display zeros.

Does it work as expected?

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

Kenja824 (Automotive) · Answer 6 · 2016-12-29 20:13:02

Wow, I even looked in the options for that, expecting it had to be there and couldnt find it. Im guessing it was one of my brighter moments and I didnt realize at the time that the advanced area can be scrolled down. lol

Yes, the formulas do work how I was hoping. If anyone ever has time to write the code for it I would still like to have that for my learning purposes, but for now this does work. Thanks Onlyadrafter. You might need to change your handle to Morethanadrafter soon. lol

Kenja824 (Automotive) · Answer 7 · 2016-12-29 20:22:26

Also, just to add to this, I was able to use some of my old formula and make it so we dont need the formula in column N anymore. The formula in O10 is now .... =IF(WEEKDAY(A10)=2,K10,SUM(K10+O9))

onlyadrafter (Mechanical) · Answer 8 · 2016-12-29 21:13:01

Hello,

Does this work as expected?

Column N needs to be empty though.

CODE

Sub TRACK_HOURS()
    Application.ScreenUpdating = False
'PUT WEEK NUMBER IN COL N
    For MY_ROWS = 10 To Range("A" & Rows.Count).End(xlUp).Row
        Range("N" & MY_ROWS).Formula = "=weeknum(A" & MY_ROWS & ",2)"
    Next MY_ROWS
'CALCULATE HOURS
    MY_CURRENT_WEEK = Range("N10").Value
    For MY_ROWS = 10 To Range("A" & Rows.Count).End(xlUp).Row
        MY_TOTAL = MY_TOTAL + Range("K" & MY_ROWS).Value
        If Range("N" & MY_ROWS + 1).Value <> Range("N" & MY_ROWS).Value Then
            If MY_TOTAL <= 40 Then
                Range("L" & MY_ROWS).Value = Range("K" & MY_ROWS).Value
                MY_TOTAL = 0
            Else
                If MY_TOTAL <= 40 Then
                    Range("L" & MY_ROWS).Value = Range("K" & MY_ROWS).Value
                Else
                    Range("M" & MY_ROWS).Value = Range("K" & MY_ROWS).Value
                    MY_TOTAL = 0
                End If
            End If
        Else
            If MY_TOTAL <= 40 Then
                Range("L" & MY_ROWS).Value = Range("K" & MY_ROWS).Value
            Else
                If Range("M" & MY_ROWS - 1).Value > 0 Then
                    Range("M" & MY_ROWS).Value = Range("K" & MY_ROWS).Value
                Else
                    Range("M" & MY_ROWS).Value = MY_TOTAL - 40 'Range("K" & MY_ROWS).Value
                    Range("L" & MY_ROWS).Value = 40 - (MY_TOTAL - Range("K" & MY_ROWS).Value)
                End If
                If Range("N" & MY_ROWS + 1).Value <> Range("N" & MY_ROWS).Value Then
                    MY_TOTAL = 0
                End If
            End If
        End If
    Next MY_ROWS
        Columns("N:N").ClearContents
        Application.ScreenUpdating = True
End Sub

I'm sure this code can be shortened, but it's been a really long day.

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

Kenja824 (Automotive) · Answer 9 · 2016-12-29 22:12:02

Ok, here is where I have to be most honest..... I have no idea if this works right. lol This is how green I am with VBA Code. I was thinking more in the lines of modules I would add and then click on the cell, hit = and find the new function. lol The more I think on it, I can see where that would need multiple functions and such. I sat there trying to find this one when I hit the = and finally realized it is ran as a macro.

So I saved where I was and hit run and it flickered but I have no idea what it did if it did anything. The problem is that I have been messing with my sheet for a bit and added columns and such. The only way I will know if it works is if I go through the code and see what exactly it does and change my excel form to be set in a way that I will know if it actually did something or not. And I leave in a few and will be off until after the New Year.

Man I wish I had excel at home to mess with this stuff. lol

However, the important thing is, I will get to go through this when I can and if nothing else will learn from the code and that is what I wanted more than anything.

I really appreciate everything Onlyadrafter. The formula help got me on track for what I wanted to do and I will learn what I can from the code when I get back to it. Hope you have an awesome New Year and a better whole Year.

SkipVought (Aerospace) · Answer 10 · 2016-12-29 23:48:54

Two comments.

1) www.Tek-Tips.com (you should see this link just under the URL bar, alon with Eng-Tips and Engineering.com) is where you can get lots of coding & spreadsheet tips.

2) you really ought to upload a sample workbook or at least post a useable copy 'n' past sample. If you won't take the time to do that, many of us won't waste the time transcribing your data picture into a useable table.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

SkipVought (Aerospace) · Answer 11 · 2016-12-30 15:22:52

BTW, a spreadsheet solution...


Date	Day	WkNo	HRS	ST	OT
Jan 02	Mon	2	10	10	0
Jan 03	Tue	2	8	18	0
Jan 04	Wed	2	12	30	0
Jan 05	Thu	2	8	38	0
Jan 06	Fri	2	8	40	6
Jan 07	Sat	2	10	40	16
Jan 08	Sun	2	10	40	26
Jan 09	Mon	3	8	8	0
Jan 10	Tue	3	8	16	0
Jan 11	Wed	3	8	24	0
Jan 12	Thu	3	9	33	0
Jan 13	Fri	3	9	40	2
Jan 14	Sat	3	9	40	11

ST E4: =IF(SUMPRODUCT(($C$4:C4=C4)*($D$4:D4))<=40,SUMPRODUCT(($C$4:C4=C4)*($D$4:D4)),40)
OT F4: =SUMPRODUCT(($C$4:C4=C4)*($D$4:D4))-E4

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

SkipVought (Aerospace) · Answer 12 · 2016-12-30 15:50:36

What would be even easier is to just track your hours by day using Named Ranges...


Date	Day	WkNo	HRS
Jan 02	Mon	2	8
Jan 03	Tue	2	8
Jan 04	Wed	2	8
Jan 05	Thu	2	8
Jan 06	Fri	2	8
Jan 07	Sat	2	0
Jan 08	Sun	2	0
Jan 09	Mon	3	8
Jan 10	Tue	3	8
Jan 11	Wed	3	8
Jan 12	Thu	3	9
Jan 13	Fri	3	9
Jan 14	Sat	3	9
Jan 15	Sun	3	9
Jan 16	Mon	4	10
Jan 17	Tue	4	10
Jan 18	Wed	4	12
Jan 19	Thu	4	8
Jan 20	Fri	4	10
Jan 21	Sat	4	10
Jan 22	Sun	4	8
Jan 23	Mon	5	8
Jan 24	Tue	5	12
Jan 25	Wed	5	8
Jan 26	Thu	5	8
Jan 27	Fri	5	8
Jan 28	Sat	5	12
Jan 29	Sun	5	10
Jan 30	Mon	6	9
Jan 31	Tue	6	8
Feb 01	Wed	6	8
Feb 02	Thu	6	12
Feb 03	Fri	6	10
Feb 04	Sat	6	10

Then just have a summary table like this using the Named Ranges in the table above...


Week Of 	HRS	ST	OT
1/2/2017	40	40	0
1/9/2017	60	40	20
1/16/2017	68	40	28
1/23/2017	66	40	26
1/30/2017	57	40	17

...where

HRS J4: =SUMPRODUCT((WkNo=WEEKNUM($I4,2))*(HRS))
ST K4: =IF(J4>40,40,J4)
OT L4: =J4-K4

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Come Join Us!

Posting Guidelines

VBA CODE - Tracking work hours