Macro for time sheet

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

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.

Bob Phillips said:

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.

Click to expand...

Thank you Bob. I will give this a try. Will get back to you. You have been most prompt. Regards

Monkey

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