Results 1 to 6 of 6

Thread: Get minute difference between working days and hours
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Get minute difference between working days and hours

    Hi Everyone,

    Would like to seek for your help on how can I get minute difference between two dates considering working days and working hours

    Working days - Monday to Friday
    Working Hours - 8AM - 6PM

    Sample Scenario

    Start Date Finished Date Desired Result in minutes
    2/14/2018 6:30:00 PM 2/14/2018 8:45:00 AM 45 mins
    2/15/2018 6:00:00 PM 2/18/2018 8:30:00 AM 30 mins

    Thank you in advance!

  2. #2
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get minute difference between working days and hours

    Hi Elli,
    You could try this formula
    Code:
    =((((B2-A2)-(DAYS(B2,A2)+1-NETWORKDAYS.INTL(A2,B2,1)))*24)-10)*60
    Where A2 has the start datetime and B2 has the end datetime
    Cheers
    Sergio

  3. #3
    New Member
    Join Date
    May 2016
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get minute difference between working days and hours

    Hi. How could I exclude those time (minutes) from 6:00pm - 8am?

  4. #4
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get minute difference between working days and hours

    That is what the formula does, it excludes time from 6 pm to 8 am

  5. #5
    New Member
    Join Date
    May 2016
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get minute difference between working days and hours

    Quote Originally Posted by sergioMabres View Post
    That is what the formula does, it excludes time from 6 pm to 8 am
    Hi I tried to simulate based on my given example above but I got -1185 and 270 and not the desired result in my query. Thanks.

  6. #6
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    946
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get minute difference between working days and hours

    oh yes you are right, your not working hours are 14 not 10 in your case, so the formula is
    Code:
    =((((B2-A2)-(DAYS(B2,A2)+1-NETWORKDAYS.INTL(A2,B2,1)))*24)-14)*60
    Btw your first row data start date time is greater then finished date time you shod correct this
    Cheers
    Sergio
    Last edited by sergioMabres; Apr 4th, 2019 at 07:52 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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