Results 1 to 3 of 3

Thread: Please chcek the formula - night shift tracker

  1. #1

    Please chcek the formula - night shift tracker



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Experts

    Need your help to create a tracker to check the status of task completion. Please check below heads need to consider.


    A2 :Receive date& time

    B2: Completion date & time

    Weekdays : Mon -Fri

    Working hours: 9.30 (17.00-2.30)

    Holidays

    Formula should exclude weekends(Sat&SUN) and non working hours

    based on the above details need to consider to fine the completion in days, hour,mintue manner.

    When i tried in web and created a formula it's not showing result correctly

    =INT(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&" Days,"&HOUR(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&" hrs, "&MINUTE(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&" min and "&SECOND(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&"secs"


    Thanks in Advance

    Srinath

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Have you tried using the Evaluate Formula procedure to pinpoint the problem? Can you provide an example of the date/time in A2 and B2, along with the expected result and the incorrect result that your formula is returning ?

  3. #3
    Hi,

    Try the following formula in C2:

    =NETWORKDAYS(A2,B2,$H$2:$H$5)*(9.5/24)+NETWORKDAYS(B2,B2,$H$2:$H$5)*(IF(MOD(B2,1)>=17/24,MOD(B2,1)-17/24,IF(MOD(B2,1)<2.5/24,MOD(B2,1),0))-(9.5/24))+NETWORKDAYS(A2,A2,$H$2:$H$5)*(IF(MOD(A2,1)>=17/24,1-MOD(A2,1)+2.5/24,IF(MOD(A2,1)<2.5/24,2.5/24-MOD(A2,1),0))-(9.5/24))

    This is considering that you have - Start Time in A2, End Time in B2, List of holidays excluding weekends are on H2:H6

Posting Permissions

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