Results 1 to 3 of 3

Thread: Help with time duration calculation

  1. #1
    Neophyte TN0410's Avatar
    Join Date
    May 2022
    Posts
    1
    Articles
    0
    Excel Version
    Microsoft 365 MSO Version 2203

    Help with time duration calculation



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

    Hello All,

    Please I need some assistance with a formula to calculate the time duration between 2 dates with the following conditions:

    - weekends are excluded

    - work hours are between 8AM and 5PM (any request received after 5PM is treated as next day

    I used this formula to get the difference between the 2 dates, =TEXT(C3-A3,"d:h:mm:ss") but I've been stuck at factoring the conditions. Im using the formula to measure adherence to SLA.

    Any help will be appreciated. Cheers

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,123
    Articles
    0
    Excel Version
    365
    See the first formula here: https://exceljet.net/formula/get-wor...ates-and-times with explanations.
    You'll probably need a bit more help to get the output as you'd like.
    Things to consider include
    how to display for example 13.5 hours? As 1.5 days (so you can easily do arithmetic on multiple results)? Perhaps as 1 day and 4:30hrs (much more difficult to do arithmetic on)
    include public holidays (and/or company holidays).

    The formula can get quite complicated, although more recent versions of Excel can simplify it, depending on your version of Excel; what is your version? Does it include the function LET on a sheet?
    Another way would be to design a user defined function which would involve enabling macros; is that a possibility?

    Best attach a workbook with some dates and times with expected results (manually calculated so that we can test the formula against them) - make the dates so that they really test the formula and try to make it go wrong!

    Questions to answer in red.

  3. #3
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    43
    Articles
    0
    Excel Version
    2016
    it will be better for others to understand your question with sample data including original data and expected result.

Tags for this Thread

Posting Permissions

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