# Calculate time taken from earliest start time and latest end time

1. ## 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!

2. ## Re: Calculate time taken from earliest start time and latest end time

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

or do i mis something ?

Kind regards
Leo

3. ## Re: Calculate time taken from earliest start time and latest end time

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.

4. ## Re: Calculate time taken from earliest start time and latest end time

``Please Login or Register  to view this content.``
kind regards
Leo

5. ## Re: Calculate time taken from earliest start time and latest end time

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?

6. ## Re: Calculate time taken from earliest start time and latest end time

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.

7. ## Re: Calculate time taken from earliest start time and latest end time

See this line

With Sheets("Result")

change to

With Sheets("Results")

Kind regards
Leo

8. ## Re: Calculate time taken from earliest start time and latest end time

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!!

9. ## Re: Calculate time taken from earliest start time and latest end time

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

10. ## Re: Calculate time taken from earliest start time and latest end time

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.

11. ## Re: Calculate time taken from earliest start time and latest end time

What with row 9 ?

Kind regards
Leo

12. ## Re: Calculate time taken from earliest start time and latest end time

Try this:

``Please Login or Register  to view this content.``

13. ## Re: Calculate time taken from earliest start time and latest end time

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.

14. ## Re: Calculate time taken from earliest start time and latest end time

I think xladept provided perfect solution in # 12

Kind regards
Leo

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1