Need formula to calculate time in hours between two date ranges

Hi,

I need a formula to calculate the time in hours/minutes for the below situation.

I have A1, B1 cells with open and closed dates with time stamp. Now i need the difference between two dates in hours/minutes with criteria 1) only workdays should be calculated 2) only hours/minutes should be calculated between 10am to 8pm.

I need the output like below

Opened Closed Time taken in hours
3/3/11 8:39 3/3/11 18:30 6.3
3/1/11 14:30 3/2/11 19:00 12.3

Please help me.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Hi

Does the attached workbook do what you want?

I don't make the results the same as you show however, as the first line results in 8:30 hours and the second 18:30 hours

Originally Posted by annyfouanny

Hi Roger,

Thank you for your response. How ever I dint get the anticipated result. I have added sample workbook that what i am looking for. Can please have a look and help me.

Thank you!!!
Anil.

Hi Anil

I have changed the entries on my workbook to match yours, and I still believe that your calculation is incorrect.

take example 1
The only effective time is from 10:00 to 18:30 which is 8:30 hours

In example 2
On day 1 the time is from 14:30 to 20:00 which is 5:30 hours
On day 2 it is from 10:00 to 19:00 which is 9:00 hours
giving a total of 14:30 hours