## VBA CODE - Tracking work hours

## VBA CODE - Tracking work hours

(OP)

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.

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

## RE: VBA CODE - Tracking work hours

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

## RE: VBA CODE - Tracking work hours

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.

## RE: VBA CODE - Tracking work hours

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

## RE: VBA CODE - Tracking work hours

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

## RE: VBA CODE - Tracking work hours

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

## RE: VBA CODE - Tracking work hours

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

## RE: VBA CODE - Tracking work hours

## RE: VBA CODE - Tracking work hours

Does this work as expected?

Column N needs to be empty though.

## CODE

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

## RE: VBA CODE - Tracking work hours

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.

## RE: VBA CODE - Tracking work hours

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

pictureinto a useable table.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: VBA CODE - Tracking work hours

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))-E4Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: VBA CODE - Tracking work hours

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

...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!}