Calculate total clocked In minutes in a given hour

Hello:

Please refer to attached sheet.

I will be ok with either VB Macro or Formula to perform this task.

I have clockIn-ClockOut information for employees in row 2 thru 16

I want to use this data and separate out to see how many minutes are employees clocked in a given hour.
For example,
on 7/1/2013,
Angela Clockin at 954 and Clocked Out at 1403
Tomas Clockin at 1003 and Clocked Out at 1132

Between
901-1000 the total minutes would be 6
1001-1100 the total minutes worked would be 60+57 (60 min for Angela and 57 Min for Tomas)
1101-1200 the total minutes worked would be 60+32 (60 min for Angela and 32 Min for Tomas)
1201-1300 the total minutes worked would be 60 (60 min for Angela)
1301-1400 the total minutes worked would be 60 (60 min for Angela)
1401-1403 the total minutes worked would be 3 (3 min for Angela)


I have manually calculated minutes for each hour and entered in column D Cell D20:D25

I hope I have explained very clearly.

Please let me know if you any questions.

Thank you

Riz Momin

Hi tehneXus or Any exceler:

Thank you so much for your help.
This code work great for me.
However i like to see if you can or someone can help me make small change in the code so that the result does not in separate "Result" sheet but goes in the same "Sheet1" in cell D20:D34, H20:H34, L20:L34 and so on.
Please refer to earlier attached excel file "Book5.xls

Thank you so much for help.

Riz Momin

Hi :

Please refer to attached sheet.
Your code works great but still need some modification so that it will work with my existing excel file.

The purpose of this is to calculate the payroll for each hour.

Please help change the code to accomplish following:

1.Employee data starts in cell O45
2.Time Range start in cell J609:K621 and change range from 10am till 11pm instead 9am till 12pm
3.Now I need to multiply the number of minutes by .125 ( $7.50/hour)
4.Calculated (minutes*0.125) for each hour to be pasted in cell O609:O621, S609:S621, and so on.
5. I see one bug, every time you run the macro, it creates new data. It needs to overwite in the same cell.

I hope this is clear explanation.
Please let me know if any questions.

Thank you for all your help.

Riz Momin

Hi :

Please refer to ATTACHED EXCEL FILE.
Go to Sheet1.
There is error in minute calculation with the new code.
Result from new code is showing in row 609 thru 621 Column M onward.

Compare this result with previous code which was calculating minutes correctly (Result in row 626 onward)

Please LET ME KNOW IF ANY QEUSTIONS.

Thank you

Riz Momin

Hi tehNexus:

Once again thank you for your help.
I have made small modification in the code to make it work with my sheet, please refer to attached sheet. (i have right now got at row 66 but i will change later to go to row 608)
Looks like i got it, however I see 2 problems as of right now.
Please use the code with my modification and help me correct following issues.

1. I will have other data in Column P,Q,T,U,X,Y and so on, so when running the macro, i want to
make sure that these data's are not deleted. When we run macro, need to delete only minutes and $ data in column N,O,R,S, and so on.
2. Need 2 digit in $ column O,S,W and so on with $ sign.
3. I do not want to change the width of the columns, please keep column width same as original file.
4. Date is coming as dd.mm.yyyy and this needs to be mm/dd/yyyy
Please let me know if any questions.

Thank you

Riz Momin

Hi tennexus:

It looks like this will work great for me.
I think i have ironed out all issues and seems to work.
I will keep this tread open for right now and will close as SOLVE once I test it thoroughly.

Once again thank you so much for your kind generous fast help and will be in contact with you if needed.

Riz Momin