+ Reply to Thread
Results 1 to 14 of 14

Calculate time taken from earliest start time and latest end time

  1. #1
    Registered User
    Join Date
    05-20-2012
    Location
    Taiwan
    MS-Off Ver
    Office 365
    Posts
    47

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

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    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. #3
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,006

    Re: Calculate time taken from earliest start time and latest end time

    Quote Originally Posted by escapes88 View Post
    ... 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. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Calculate time taken from earliest start time and latest end time

    Please Login or Register  to view this content.
    kind regards
    Leo
    Last edited by LeoTaxi; 04-11-2018 at 06:38 AM.

  5. #5
    Registered User
    Join Date
    05-20-2012
    Location
    Taiwan
    MS-Off Ver
    Office 365
    Posts
    47

    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. #6
    Registered User
    Join Date
    05-20-2012
    Location
    Taiwan
    MS-Off Ver
    Office 365
    Posts
    47

    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. #7
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    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. #8
    Registered User
    Join Date
    05-20-2012
    Location
    Taiwan
    MS-Off Ver
    Office 365
    Posts
    47

    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. #9
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    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. #10
    Registered User
    Join Date
    05-20-2012
    Location
    Taiwan
    MS-Off Ver
    Office 365
    Posts
    47

    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. #11
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Calculate time taken from earliest start time and latest end time

    What with row 9 ?

    Kind regards
    Leo
    Attached Images Attached Images

  12. #12
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Calculate time taken from earliest start time and latest end time

    Try this:

    Please Login or Register  to view this content.
    Last edited by xladept; 04-12-2018 at 12:10 PM. Reason: Detail
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  13. #13
    Registered User
    Join Date
    05-20-2012
    Location
    Taiwan
    MS-Off Ver
    Office 365
    Posts
    47

    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. #14
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Calculate time taken from earliest start time and latest end time

    I think xladept provided perfect solution in # 12


    Kind regards
    Leo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 04-25-2020, 03:23 AM
  2. [SOLVED] Formula to return earliest and latest time.
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-07-2014, 01:35 AM
  3. Replies: 3
    Last Post: 08-31-2014, 06:39 AM
  4. Looking Up Earliest and Latest Time Values
    By warhead92100 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-25-2013, 06:07 AM
  5. How to Get Earliest Time In and Latest Time Out
    By domzki225 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-20-2013, 04:55 PM
  6. Earliest and Latest Time where cell contains certain text
    By asalbus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2013, 01:45 AM
  7. [SOLVED] Calculate time durations for a roster, and indentify the earliest and latest times
    By bajdr47 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 08-25-2012, 04:47 PM

Bookmarks

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