+ Reply to Thread
Results 1 to 7 of 7

Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

  1. #1
    Registered User
    Join Date
    02-20-2018
    Location
    Cleveland
    MS-Off Ver
    Office 2016
    Posts
    4

    Unhappy Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

    I have one that I haven't seen anywhere. Help!

    I'm trying to calculate "x Business Days from an entered date" ("A1"), where "entered date" is formatted with a timestamp (mm/dd/yy hh:mm AM/PM).
    If I use WORKDAY, my result does not retain the timestamp (mm/dd/yy 12:00AM)
    Example: Using WORKDAY to add 2 business days to "02/20/18 02:30 PM" results in "02/22/18 12:00 AM". I need it to show "02/22/18 02:30 PM".

    My formula is as follows:
    =WORKDAY(A1,2,H2:H22)

    I've tried adding multiple hours instead of days (A1 + 48/24) but it does not respect the business hours. It can't be this hard. How do I get WORKDAY to respect the timestamp?

    Thank you,
    Lizz

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

    Hello and welcome to the forum.

    Assuming that the time of the first day is during regular business hours, you can do this:

    =WORKDAY(A1,2)+MOD(A1,1)

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

    Workday as function name suggests, only deal with whole day and ignores any time fraction.

    If you just want to add 2 business days and retain 02:30 PM portion... Use the formula given by 63falcondude.

    If you need to consider fraction of work day as in business hours... you'll need significantly more complex calculation.
    I typically fall back to VBA, since I have hard time keeping track of formula
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    02-20-2018
    Location
    Cleveland
    MS-Off Ver
    Office 2016
    Posts
    4

    Re: Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

    Thank you - that solved the problem very nicely. I'm grateful for the help!

  5. #5
    Registered User
    Join Date
    02-20-2018
    Location
    Cleveland
    MS-Off Ver
    Office 2016
    Posts
    4

    Re: Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

    Quote Originally Posted by CK76 View Post
    Workday as function name suggests, only deal with whole day and ignores any time fraction.

    If you just want to add 2 business days and retain 02:30 PM portion... Use the formula given by 63falcondude.

    If you need to consider fraction of work day as in business hours... you'll need significantly more complex calculation.
    I typically fall back to VBA, since I have hard time keeping track of formula
    Intriguing! I have a business use case for that formula, but it would be the rare exception and not the rule. I better stick with the simple, ha!

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

    Glad we could help.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    FYI (Business Hours): https://www.extendoffice.com/documen...ays-hours.html

  7. #7
    Registered User
    Join Date
    02-20-2018
    Location
    Cleveland
    MS-Off Ver
    Office 2016
    Posts
    4

    Re: Add or Subtract Business Hours in WORKDAY Calculation w/Timestamp

    Thank you!
    Last edited by AliGW; 05-21-2022 at 05:10 AM. Reason: PLEASE don't quote unnecessarily!

+ 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. [SOLVED] Excel Workday function to exclude non business hours
    By Sele in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2016, 05:20 AM
  2. [SOLVED] Adding hours to start time - Business hours/holidays/weekends
    By Thunderer in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2014, 01:09 PM
  3. Calculation of business hours exculding sundays and holidays
    By balundl in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-30-2014, 01:17 PM
  4. Replies: 5
    Last Post: 10-08-2012, 05:18 AM
  5. Calculation of business hours
    By sreerag446 in forum Excel General
    Replies: 5
    Last Post: 04-30-2009, 04:27 PM
  6. "Business Hours" Time Calculation
    By Skoal in forum Excel General
    Replies: 3
    Last Post: 10-24-2007, 12:21 PM

Tags for this Thread

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