Calculate time taken from earliest start time and latest end time

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!

why the result has to be 9:19 ?
in my calculation it is 8:37

or do i mis something ?

Kind regards
Leo

Originally Posted by escapes88

... see for Mr A, for 2/4/2018, ... difference between 8:01 AM and 5:21 PM ... get 9hrs 19mins (9:19)... formula ((C18-F17+(C18<F17))*24)-G18)*60/1440 ...

???

Rather: =(((C5-F2+(C5<F2))*24)-G5)*60/1440

but it's "Giant slalom" ...

It's enough: =C5-F2

Why a macro here (?), functions are enough.

Hi Leo

The reason why it is 9.19 because for the same work day, 2/4/2018, Mr A's latest end work is 17:21 (5:21PM) while his earliest start work is 8:01.

Applying the formula, he worked for 9hrs and 19 mins.

If there was "1" at the deduction column then it will be treated as 1 hr, thus he would had worked for 8 hrs 19mins.

Basically, if I need to know the total time spent by Mr A for each day, then should there be more than one entry in a day, the result should take the difference from the earliest Start Work and latest End Work.

Thanks, I tried the codes but there is an error in the results - "#NAME?".

I also need a header in the results and the borders.

Would you be able to help?

Hi porucha vevrku

The issue i face is, there is no formula which can find the total time spent by Mr A for each day should there are more than one entry in a day. The formula is only okay when there is just one entry. If there is more than one entry, then the result should take the difference from the earliest Start Work and latest End Work.

Thank you.

See this line

With Sheets("Result")

change to

With Sheets("Results")

Kind regards
Leo

Hi Leo

I dont know why its still giving me that same error.

Also I need a border around with the headers, Name, End Work (DD/MM/YYYY & Time), Time Out, ID, Start Work *DD/MM/YYYYY & Time), Time In, Deduction, Total Time (hrs & mins).

Basically I need the macro to intelligently find the latest time for the End Work (DD/MM/YYYY & Time) of the day to the respective earlist Start Work (DD/MM/YYYY & Time).

Could you see if its possible?

Many thanks!!

Yes the borders are a big issue pffff.....
first let us know why in your exemple file sheet result Mr A, the date 6/04/2018 is not present ?

start with exemple file with exemple sheet before and exemple sheet after, everything included for both sheets
and second sure you have version 2016 ?

Kind regards
Leo

Hi Leo

In my example provided, some staff might not be present for all the days the others were present. So on 6/04/2018, Mr A was not present.

In my date.xls file provided above, the "Original" spreadsheet is what my existing data would be. Wherelse the "Results" is what I need the macro to do for me.

The tricky part of this whole macro is;

1) Given a certain date, the macro should intelligently take the later timing of the End Work and minus off the earlier timing of the Start Work.
2) Using the formula above, it will calculate the Hrs & Mins taken.

Yes, I am using version 2016.

Thank you so much for understanding what I need.

What with row 9 ?

Kind regards
Leo

Hi Leo

I got what you meant. The 6/4/2018 for Mr A was not reflected in the Results page. It should have. Thanks for pointing this.

Please assume that there was no entry for Mr A in 6/4/2018.

I think xladept provided perfect solution in # 12


Kind regards
Leo