Time Calculation

Project Start Time 22-04-2015 12:30:00
Project End Time 24-04-2015 20:30:00

Shift Start Time 11:00:00
Shift End Time 21:30:00

The total time spent on the project is 29 hours but a simple subtraction is not working and is giving me 56 hours as its taking time beyond Shift End Time 21:30:00.

Any suggestions on how to get this regularized.

Hi,
plz see the file.

Assuming the start date & time is in A2, and the end date & time is in B2

=(NETWORKDAYS(A2,B2)-1)*("21:30"-"11:00")
+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"21:30","11:00"),"21:30")
-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"21:30","11:00")