Results 1 to 4 of 4

Thread: Macro for time sheet

  1. #1

    Macro for time sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all. I wonder if you can help? T & A system delivers report in .csv format. This is my problem and I cant seem to crack it.

    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

    At the moment I am doing all the above manually.

    Attached is a copy of the .csv file that we get from the system. 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.

    I have attached it as an excel. file
    Attached Files Attached Files

  2. #2
    See if this formula does the trick

    =SUM(IF($B$2:$B$241="Ephraim",IF((($G$2:$G$241="#--:--")*($H$2:$H$241="#--:--")),0,IF((($G$2:$G$241="#--:--")+($H$2:$H$241="#--:--")),--"08:00:00",$I$2:$I$241))))

    it is an array formula, so it must be array-entered.

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    See if this formula does the trick

    =SUM(IF($B$2:$B$241="Ephraim",IF((($G$2:$G$241="#--:--")*($H$2:$H$241="#--:--")),0,IF((($G$2:$G$241="#--:--")+($H$2:$H$241="#--:--")),--"08:00:00",$I$2:$I$241))))

    it is an array formula, so it must be array-entered.
    Thank you Bob. I will give this a try. Will get back to you. You have been most prompt. Regards

    Monkey

  4. #4

    Absolute Reference?

    Hiya. The Absolute reference poses a problem. There are several employees. I will work on this one though. Many thanks for your kind assistance.

    MN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •