Microsoft Excel - Time formula between two dates/times(or same date) but only want ...

Hello,

I am trying to to write a formula that would give me the Hours/Mins it takes to underwrite a file using 2 date/time fields.  The trick is I that I only want to count the time from 8 to 5 each day.  Anytime outside of those hours I don't want.

Right now I have 2 columns: 

It's giving me the Cycle time (column BD but it inlcudes the full 24 hour clock)

How do I get Columns BB and BC to give the the total hours and minutes it takes to Underwrite the loan but only between the hours of 8 AM and 5 PM?

Right now the formula that I am using to get BD is:
=INT(BB5-BC5)*24+(((BB5-BC5)-INT(BB5-BC5))/0.04166666)

I have attached an image of the columns,.....well I think I attached it correctly.

Column BB           Column BC          Column BD
4/21/14 11:57AM      4/18/14 4:27PM        67.50 Hours  (want it to only calculate the 8-5 hours so would be 3.95)

Thanks for any of your assistance.

Hello Wendy,
Try this

=((BB5-INT(BB5))-8/24+17/24-(BC5-INT(BC5)))*24 +(NETWORKDAYS(BC5,BB5)-2)*9

Regards
Harry

Hello again Wendy,
Forgot to mention that the formula assumes that the job start and finish times are also between 8 and 5.
Harry

Thanks so much for your response....

I just tried it and the cycle times are coming out negative????

Any ideas?

For example:

Disposition Date & Time        Log Date & Time      Cycle Time
Column BB                     Column BC            

4/22/14 2:21PM                4/22/14 9:51AM        -17.8766

When the cycle time should be 4.51 approx.  The file was logged into the system at 9:51 AM and approved at 2:21 PM same day all within the 8-5 hr for this example but all others outside of the 8-5 are also coming up negative.

Thanks again!!!!

Wendy   

Hi Wendy,
The formula is working absolutely fine for that data giving an answer of 4.5.  Are you sure that you copied it exactly?
Here it is again.  If you select the formula in this reply and copy and then paste into your spreadsheet you should be sure that it is correct.
=((BB5-INT(BB5))-8/24+17/24-(BC5-INT(BC5)))*24 +(NETWORKDAYS(BC5,BB5)-2)*9
Regards
Harry

Hello and Thank you!  What if the job start times & Finish times are after 5 pm and before 8 Am but I only want to include the hours between 8-5 worked.

Did not know if you could help me with this formula?  I would appreciate it.  Just can’t seem to get it right.

 

I am trying to only count the hours between 8-5 and sometimes it will need to read different dates.

Example one

Column BF: 6/19/14 7:17PM

Column BG 6/19/14 4:16PM

Column BH 3.03

I want it to only ready .73 which is 47 minutes / 60 minutes to give me the cycle time. The formula that I have now to get the 3.03 is =INT(BF20-BG20)*24+(((BF20-BG20)-INT(BF20-BG20))/0.04166666)

 

Example two: 

6/19/148:23AM

6/18/14 :31PM

16.87

But I need it to only account from 3:31 on the 18^th to 5:00 and then pick back up at 8:00 am on the 19^th?

Hi Wendy,
Try this.

=((MAX(BF20,INT(BF20)+8/24)-INT(BF20))-8/24+17/24-(MIN(BG20,INT(BG20)+17/24)-INT(BG20)))*24 +(NETWORKDAYS(BG20,BF20)-2)*9

It works for your two examples but I have not tried it for other cases.
Regards
Harry