• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Time Sheet Macro with date/time data format

MonkeyNut

Member
Hi all, Could do with some help here. T&A Clock In Reports come in as csv files. Am stuck on the following:

This T&A system does not give the reports out in a very user friendly way.

Ultimately there are only 3 major things I’m looking for.

· Start time must be 6am, regardless of what time they clocked in, finish time is whenever they log out

· If the person clocks in and not out, or clocks out and not in, we must allocate them 8 working hours

· Based on the above assumptions, a sum total of all hours worked per member of staff

Column I is the one with the total time per day, however, it starts before 06h00 if they clock before then, and I cannot do any sumif formulas to get the total hours worked as the format of the data seems to be a date/time one.

Doing this manually is time consuming.

Attached is a copy of the .csv file saved as an excel file that we get from the system.

Any assistance would be greatly appreciated.

Thanks in advance
MonkeyNut
 

Attachments

  • OUTPUT_PERIOD_RESULT_20140710.xlsx
    22.2 KB · Views: 16
Hi,

Try below formula in N2 and copy down:

=IF(H2=G2,0,IF(AND(OR(IFERROR(TIMEVALUE(G2)>0,0),IFERROR(TIMEVALUE(H2),0)>0),OR(G2="#--:--",H2="#--:--")),TIMEVALUE("08:00:00"),TIMEVALUE(H2)-MAX(6/24,TIMEVALUE(G2))))

Regards,
 
Hi,

Try below formula in N2 and copy down:

=IF(H2=G2,0,IF(AND(OR(IFERROR(TIMEVALUE(G2)>0,0),IFERROR(TIMEVALUE(H2),0)>0),OR(G2="#--:--",H2="#--:--")),TIMEVALUE("08:00:00"),TIMEVALUE(H2)-MAX(6/24,TIMEVALUE(G2))))

Regards,
Thank you Somendra. You have been real quick to respond. I will let you know when I get home if this worked. Have a great day. Monkey
 
@MonkeyNut

Try below code:

Code:
Sub test()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr
    If Cells(i, 7) = Cells(i, 8) Then
        Cells(i, 15).Value = 0
    Else
        If Trim(Cells(i, 7)) > 0 And Cells(i, 8).Value = "#--:--" Then
        Cells(i, 15).Value = TimeValue("08:00:00")
    Else
        If TimeValue(Cells(i, 8).Value) > 0 And Cells(i, 7).Value = "#--:--" Then
        Cells(i, 15).Value = TimeValue("08:00:00")
    Else
        Cells(i, 15) = TimeValue(Cells(i, 8)) - WorksheetFunction.Max(6 / 24, TimeValue(Cells(i, 7)))
    End If
    End If
    End If
Next i

   
End Sub

This will put the output in column O. Change 15 to your required cell number and format the cell to [hh]:mm.

Regards,
 
Thank you. This works like a dream. I have one other issue, I thought I needed to show the output in date/time but all I need is to calculate this in numeric. I just have to calculate the number of hours. Is it possible to put this in to the macro? Because when I go an format the cells it is showing 0.34. Many thanks again Somendra. MN
 
TRY multiplying by 24 in the code where ever there is Cells(i,15)= [old code] *24. Like below
Cells(i, 15).Value = 0

Cells(i, 15).Value = TimeValue("08:00:00") *24

Cells(i, 15).Value = TimeValue("08:00:00")*24

Cells(i, 15) = (TimeValue(Cells(i, 8)) - WorksheetFunction.Max(6 / 24, TimeValue(Cells(i, 7)))) * 24




Regards,
 
I feel like I'm going overboard here with you Somendra, because you have been marvelous with me and very very patient, but I need to round this off? Should that be done after the macro is run? or can it be put into the macro?
 
Sorry 8.5 / 12.999 to read 12.9. Sorry I cant seem to change to column to run in Column I
 
Last edited:
See the file. Macro will put the result in in Column O and than in column I, with formula the figures will be rounded. Just check the results.

Regards,
 

Attachments

  • OUTPUT_PERIOD_RESULT_20140710.xlsm
    38.2 KB · Views: 7
Somendra You are brilliant. I thank you with all my heart. I will have to study this effort of yours extensively if I am ever going to teach it at school. Thank you once again. Love MN.
 
Somendra apologies, me again. How would I take that now to a new .csv? because although I copied the macro into the original doc I gave you. see attached. when I run the macro it has no effect on column I. I also tried autofilling the round function in column I. In the doc you sent me its all great.
 
Begging pardon. It worked sorry. Guess I just have to hide column O after everything done. Thank you Dear. Love MN
 
Try below code, which will directly put values in Column I by rounding it, so no need of Column O and Round function.

Code:
Sub test()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr
    If Cells(i, 7) = Cells(i, 8) Then
        Cells(i, 9).Value = 0
    Else
        If Trim(Cells(i, 7)) > 0 And Cells(i, 8).Value = "#--:--" Then
        Cells(i, 9).Value = Round(TimeValue("08:00:00") * 24, 1)
    Else
        If TimeValue(Cells(i, 8).Value) > 0 And Cells(i, 7).Value = "#--:--" Then
        Cells(i, 9).Value = Round(TimeValue("08:00:00") * 24, 1)
    Else
        Cells(i, 9) = Round((TimeValue(Cells(i, 8)) - WorksheetFunction.Max(6 / 24, TimeValue(Cells(i, 7)))) * 24, 1)
    End If
    End If
    End If
Next i

   
End Sub

Regards,
 
Thank you sooooo much. This is a final thing I think. How do I get to run this in each new weeks time sheets?, without having to go and create the VBA each time? Is that possible?
 
Greetings Somendra, Firstly thank you again for all your assistance. I would like to know. can one just change the vba to another column name? for instance the one you worked on was for column i. I have attached my worksheet here and highlighted column j, where my result has to show. Also does the macro name need to remain as test? Or can i change it in the vba to something else?.

Also attached the last vba you sent

p.s. Windows 8 has an issue with creating a personal file. Seems to be no fixes

Kind regards
MN
 

Attachments

  • Macro test.xlsx
    86.7 KB · Views: 9
  • Column i VBA.txt
    658 bytes · Views: 5
Hi MN,

See the file. Yes you can change macro or subroutine name.

Just in the code where it is written like

Sub Test().... change it to

Sub YourDesiredName()

See I already changed it in the file.

But note that there are some rules while creating names of subroutines.

Regards,
 

Attachments

  • Macro test.xlsm
    96.9 KB · Views: 19
Somendra thank you. Please see my first post - · Start time must be 6am, regardless of what time they clocked in, finish time is whenever they log out.

I dont know how to do that. Many thanks from sunny South Africa. MN
 
Back
Top