+ Reply to Thread
Results 1 to 11 of 11

Add number of hours to date and time field without counting in non business and Weekend hr

  1. 07-09-2015, 03:35 PM #1
    nchokshi
    nchokshi is offline
    Registered User
    Join Date
    07-09-2015
    Location
    BOSTON
    MS-Off Ver
    2013
    Posts
    6

    Add number of hours to date and time field without counting in non business and Weekend hr

    I am trying to add in number of hours to a date field in another cell. Unfortunately results includes Non business hours and weekends. Also when number of hours that I am adding are greater than 24 hours it also does not calculate properly

    Any help solving this puzzle will be greatly appreciated.

    Thanks
    Reply With QuoteRegister To Reply
  2. 07-09-2015, 03:47 PM #2
    reagan.kha
    reagan.kha is offline
    Registered User
    Join Date
    08-17-2012
    Location
    New York, New York
    MS-Off Ver
    Excel 2010
    Posts
    97

    Re: Add number of hours to date and time field without counting in non business and Weeken

    Hello please see attached for the solution. The number of hours column must be formatted as custom - [h]:mm:ss
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 07-09-2015, 04:02 PM #3
    nchokshi
    nchokshi is offline
    Registered User
    Join Date
    07-09-2015
    Location
    BOSTON
    MS-Off Ver
    2013
    Posts
    6

    Re: Add number of hours to date and time field without counting in non business and Weeken

    Thank You Reagan for your quick response. My apologies as I probably didn't ask my question properly.

    Please see below as this is what I am trying to figure out.

    Start Date # of Hours
    7/9/15 3:57 PM 10
    7/10/15 1:57 AM 5
    7/10/15 6:57 AM 20
    7/11/15 2:57 AM 25
    7/11/15 3:57 AM
    7/11/15 3:57 AM

    As you see above when I add in 20 hours to 7/10 date it automatically jumps to Saturday's date. Also when I add in greater than 23 hours resulting date is not correct.

    Appreciate your help in advance.

    Thanks
    Reply With QuoteRegister To Reply
  4. 07-09-2015, 04:12 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Add number of hours to date and time field without counting in non business and Weeken

    What formula are you using? Are we working with a 24 hour workday Monday - Friday?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  5. 07-09-2015, 04:14 PM #5
    nchokshi
    nchokshi is offline
    Registered User
    Join Date
    07-09-2015
    Location
    BOSTON
    MS-Off Ver
    2013
    Posts
    6

    Re: Add number of hours to date and time field without counting in non business and Weeken

    Workday is from 6 am to 12 pm Monday to Friday.

    Formula that I am using is A1+TIME(HOUR, MIN, SEC)

    Thanks
    Reply With QuoteRegister To Reply
  6. 07-09-2015, 04:18 PM #6
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Add number of hours to date and time field without counting in non business and Weeken

    So you don't want anything like the 2nd value, 7/10/15 1:57 AM?
    Reply With QuoteRegister To Reply
  7. 07-09-2015, 04:20 PM #7
    nchokshi
    nchokshi is offline
    Registered User
    Join Date
    07-09-2015
    Location
    BOSTON
    MS-Off Ver
    2013
    Posts
    6

    Re: Add number of hours to date and time field without counting in non business and Weeken

    I would like to get both date and time. If that is not possible I will be happy with just the date

    Thanks
    Reply With QuoteRegister To Reply
  8. 07-09-2015, 04:47 PM #8
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Add number of hours to date and time field without counting in non business and Weeken

    What I'm saying is that 7/10/2015 1:47AM is outside the regular working hours so you would want that to be 7/10/2015 7:47AM (1 hour and 47 minutes past clock in time)?

    I have formulas to do whichever way you want it
    Reply With QuoteRegister To Reply
  9. 07-09-2015, 04:50 PM #9
    nchokshi
    nchokshi is offline
    Registered User
    Join Date
    07-09-2015
    Location
    BOSTON
    MS-Off Ver
    2013
    Posts
    6

    Re: Add number of hours to date and time field without counting in non business and Weeken

    Yes please. I would like it to be 7:47am and not 1:47 am.

    Would your formualas also ignore weekends?
    Reply With QuoteRegister To Reply
  10. 07-09-2015, 04:56 PM #10
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Add number of hours to date and time field without counting in non business and Weeken

    Yes, with your first date in A3 (hours in B3)
    In A4 copied down

    =A3+B3/24+IF(INT(A3+B3/24)>INT(A3), 6/24,0)+IF(WEEKDAY(A3+B3/24+IF(INT(A3+B3/24)>INT(A3), 6/24,0),2)>5,2,0)

    Does that work for you?
    Reply With QuoteRegister To Reply
  11. 07-10-2015, 10:40 AM #11
    nchokshi
    nchokshi is offline
    Registered User
    Join Date
    07-09-2015
    Location
    BOSTON
    MS-Off Ver
    2013
    Posts
    6

    Re: Add number of hours to date and time field without counting in non business and Weeken

    This works. Thank You for your help
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Time during business hours (week days) and time during Out of Hours (weekdays+weekends)
    By wintheranders in forum Excel General
    Replies: 2
    Last Post: 11-12-2014, 11:56 AM
  2. Counting difference between hours if outside of business hours
    By joytech22 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-15-2013, 05:03 AM
  3. [SOLVED] Return historic date, if weekend, then business day before
    By chfolke in forum Excel General
    Replies: 5
    Last Post: 08-01-2012, 08:53 AM
  4. time/date tracking only during business hours
    By mmarff in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-04-2009, 06:58 PM
  5. How do I add 12 business hours to the current date/time?
    By [email protected] in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-09-2006, 07:50 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1