locked
Calculating hours in numerical formats between 2 business days, calculated column RRS feed

  • Question

  • Hi there,

    I need to calculate how many hours are between 2 days only including business days, outputting in numerical format (ex. 7.3). 

    I've been using this formula to output how many weekdays are between 2 days.

    =IF(AND((WEEKDAY(CompletedTimeStamp,2))<(WEEKDAY(,2)),((WEEKDAY(Created,2))-(WEEKDAY(CompletedTimeStamp,2)))>1),(((DATEDIF(Created,CompletedTimeStamp,"D")+1))-(FLOOR((DATEDIF(Created,CompletedTimeStamp,"D")+1)/7,1)*2)-2),(((DATEDIF(Created,CompletedTimeStamp,"D")+1))-(FLOOR((DATEDIF(Created,CompletedTimeStamp,"D")+1)/7,1)*2)))

    The problem with this is if the 2 dates are on the same day, it outputs "1". I need it to output the amount of hours then / by 24 to give me ".7"  instead.

    Is there anything I can do to convert this current formula to give me hours instead of days?

    Thanks,

    Terence

    Wednesday, May 7, 2014 6:30 PM

Answers

  • The DATEDIF is rounding to the nearest day.

    Try this:

    =IF(AND((WEEKDAY(CompletedTimeStamp,2))<(WEEKDAY(,2)),((WEEKDAY(Created,2))-(WEEKDAY(CompletedTimeStamp,2)))>1),(((CompletedTimeStamp-Created+1))-(FLOOR((CompletedTimeStamp-Created+1)/7,1)*2)-2),(((CompletedTimeStamp-Created+1))-(FLOOR((CompletedTimeStamp-Created+1)/7,1)*2))) -1

    Then above will return fractions of days, i.e.  5.5 days. Multiply this by 24 for hours.

    I think there is a extra "+1" in the above example somewhere, so I added a minus 1 to get back the correct value.


    Mike Smith TechTrainingNotes.blogspot.com

    Friday, May 9, 2014 2:56 AM

All replies

  • The DATEDIF is rounding to the nearest day.

    Try this:

    =IF(AND((WEEKDAY(CompletedTimeStamp,2))<(WEEKDAY(,2)),((WEEKDAY(Created,2))-(WEEKDAY(CompletedTimeStamp,2)))>1),(((CompletedTimeStamp-Created+1))-(FLOOR((CompletedTimeStamp-Created+1)/7,1)*2)-2),(((CompletedTimeStamp-Created+1))-(FLOOR((CompletedTimeStamp-Created+1)/7,1)*2))) -1

    Then above will return fractions of days, i.e.  5.5 days. Multiply this by 24 for hours.

    I think there is a extra "+1" in the above example somewhere, so I added a minus 1 to get back the correct value.


    Mike Smith TechTrainingNotes.blogspot.com

    Friday, May 9, 2014 2:56 AM
  • The DATEDIF is rounding to the nearest day.

    Try this:

    =IF(AND((WEEKDAY(CompletedTimeStamp,2))<(WEEKDAY(,2)),((WEEKDAY(Created,2))-(WEEKDAY(CompletedTimeStamp,2)))>1),(((CompletedTimeStamp-Created+1))-(FLOOR((CompletedTimeStamp-Created+1)/7,1)*2)-2),(((CompletedTimeStamp-Created+1))-(FLOOR((CompletedTimeStamp-Created+1)/7,1)*2))) -1

    Then above will return fractions of days, i.e.  5.5 days. Multiply this by 24 for hours.

    I think there is a extra "+1" in the above example somewhere, so I added a minus 1 to get back the correct value.

    Hi.. Mike.. I am trying to use this calcuation.. its renndering "time being used 3.3".. I need it to calcuate a work day as 8 hrs  so if

    Start Date= 9/12/2014 9:00am

    End Date= 9/16/2014 5:00 pm

    should be 3 (8 hr work days)  any way I can fix this?

    Thanks in advance for your help.


    Mike Smith TechTrainingNotes.blogspot.com




    • Edited by TCoia Monday, September 8, 2014 8:33 PM
    Monday, September 8, 2014 8:00 PM