# Calculating hours in numerical formats between 2 business days, calculated column

• ### 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

• 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?