Hi
I need help in this tricky macro, and any assistance would be really appreciated.
My spreadsheet has 2 tabs, "Original" tab and the end expected outcome should be in the "Results" tab (ignoring the red font and yellow highlights).
From the "Original" tab, I need to create a macro which would;
1) Insert 1 column beside "End Work (DD/MM/YYYY & Time)" and 1 column beside "Start Work (DD/MM/YYYY & Time)". To name it "Time Out" and "Time In" respectively.
2) Insert 1 column beside the "Time In" and name it "Deduction". Leave all column "Deduction" as blank or '0'.
3) The time would be populated in the colums "Time Out" and "Time In" (I used the formula TIME(HOUR(cell),MINUTE(cell),SECOND(cell)). Refer to the "Results" tab.
4) I need to know the total time spent by Mr A for each day. If there are more than one entry in a day, the result should take the difference from the earliest Start Work and latest End Work. In my example, if you refer to the "Results" tab, you would see for Mr A, for 2/4/2018, I had to use the difference between 8:01 AM and 5:21 PM to get 9hrs 19mins (9:19). I used the formula ((C18-F17+(C18<F17))*24)-G18)*60/1440 (which will minus the deduction column).
Many thanks for your kind assistance!
Bookmarks