+ Reply to Thread
Results 1 to 12 of 12

Computing VTO Hours

  1. #1
    Registered User
    Join Date
    05-22-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    44

    Question Computing VTO Hours

    Hi,

    Need a help on this query: I need to compute the number of Voluntary Time Off within 8 hours shift.

    Example:

    B4 = 5:00 PM - 2:00 AM <---- Shift Line

    B5 = 5:00 PM <---- log in time

    C5 = 12:11 AM <--- log out time due to VTO

    D5 = 1:49 <--- VTO hours

    How will I be able to do this to avoid computing it manually?
    Last edited by Locust; 12-11-2010 at 10:20 AM. Reason: modified title slightly

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Inquiry: Computing VTO Hours

    Assuming exact strings as outlined (ie no leading zeroes for hours preceding 10) then:

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    726

    Lightbulb Re: Computing VTO Hours

    Because the computer calculates time combined with date, you have to include date if the shift spans on two dates as mentioned in example

    in B5 & C5 enter time in m/dd/yy hh:mm format as 12/24/10 5:00 pm

    in cell D5 enter
    =INT(ABS(((C4-B4)*24)-9))
    This will calculate Hours

    In cell E5 enter
    =round(INT(ABS(((C4-B4)*24)-9)),0)
    This will calculate Hours
    This is only for VOT Hours not for Over time

    Regards

  4. #4
    Registered User
    Join Date
    05-22-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Inquiry: Computing VTO Hours

    Quote Originally Posted by DonkeyOte View Post
    Assuming exact strings as outlined (ie no leading zeroes for hours preceding 10) then:

    Please Login or Register  to view this content.
    Great, but what if agent rendered full vto shift, Im getting a result of 0 where D5 should display 8:00 <---- full shift.

  5. #5
    Registered User
    Join Date
    05-22-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Computing VTO Hours

    Quote Originally Posted by mahju View Post
    Because the computer calculates time combined with date, you have to include date if the shift spans on two dates as mentioned in example

    in B5 & C5 enter time in m/dd/yy hh:mm format as 12/24/10 5:00 pm

    in cell D5 enter
    =INT(ABS(((C4-B4)*24)-9))
    This will calculate Hours

    In cell E5 enter
    =round(INT(ABS(((C4-B4)*24)-9)),0)
    This will calculate Hours
    This is only for VOT Hours not for Over time

    Regards

    When you say VOT, means Voluntary Overtime?

    Is this also applicable on my query?

  6. #6
    Registered User
    Join Date
    05-22-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Computing VTO Hours

    bump
    bump

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Inquiry: Computing VTO Hours

    Quote Originally Posted by Locust View Post
    Great, but what if agent rendered full vto shift, Im getting a result of 0 where D5 should display 8:00 <---- full shift.
    You will need to explain further - pref. by means of examples.
    At present I can't follow your requirements given the initial post implied you wanted the difference between Actual End of Shift and Expected End of Shift - if the Actual = Expected I can't fathom why you would then want an answer of 8 rather than 0 (esp. given 5pm - 2am is 9 hours)

  8. #8
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2007 Enterprise
    Posts
    726

    Lightbulb Re: Computing VTO Hours

    Quote Originally Posted by Locust View Post
    When you say VOT, means Voluntary Overtime?

    Is this also applicable on my query?
    Sorry I mean VTO instead of VOT This means that the person goes before the actual shift
    time. It is applicable to your query

    Sorry for any confusion.

    Thanks
    Regard
    Last edited by mahju; 12-02-2010 at 06:21 AM.

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Computing VTO Hours

    Quote Originally Posted by mahju View Post
    Because the computer calculates time combined with date, you have to include date if the shift spans on two dates
    Just to be clear (in the hope of avoiding further unnecessary confusion) - as illustrated in post #2 in the context of the original question the above is not true and as such all subsequent suggestions are not warranted.
    Last edited by DonkeyOte; 12-02-2010 at 09:50 AM.

  10. #10
    Registered User
    Join Date
    05-22-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Inquiry: Computing VTO Hours

    Quote Originally Posted by DonkeyOte View Post
    You will need to explain further - pref. by means of examples.
    At present I can't follow your requirements given the initial post implied you wanted the difference between Actual End of Shift and Expected End of Shift - if the Actual = Expected I can't fathom why you would then want an answer of 8 rather than 0 (esp. given 5pm - 2am is 9 hours)
    Sorry for any confusion, I have already attached a sample template that we can utilize.

    Scenerio: I need to determine the number of Voluntary Time Off hours for the ff employees that will be based on a 8 hours shift.

    If an employee renders full VTO shift cell will display 8:00 [HH:MM].
    ( Since we all know that VTO is based from Log Off time - Remaining Hours left w/in the 8 hours shift. )

    Kindly check the attached template for reference.
    Attached Files Attached Files
    Last edited by Locust; 12-05-2010 at 10:21 AM.

  11. #11
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Computing VTO Hours

    I confess that the requirement makes little/no sense to me but formula wise those requirements would look like:

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    05-22-2010
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Computing VTO Hours

    Quote Originally Posted by DonkeyOte View Post
    I confess that the requirement makes little/no sense to me but formula wise those requirements would look like:

    Please Login or Register  to view this content.

    Sorry if the requirement does not make any sense at all to you.

    As for the formula the result is still the same:

    cell E88 = 9:00 AM-6:00 PM ◄ Shift Line
    cell I88 = 9:00 AM ◄ Log In Time
    cell J88 = 9:25 AM ◄ Log Out Time
    cell K88 = 8:35 ◄ VTO Hours were result should be 7:35


    but ill try to figure this on my own now since I know now how to compute using the formula you gave me..

    Thanks!
    Last edited by Locust; 12-05-2010 at 04:13 PM. Reason: edit

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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