How to calculate time range from 22:00 - 6:00

I think the time mess up on the example.. you could check the attachment to understand more the problem i have

Try

=MOD(D2-C2,1)

thanks for the reply but it doesnt give the value i want. i need to compute the hours he worked within that range (22:00 - 6:00).

Originally Posted by daddylonglegs

Try this formula in E2

=MOD(B2-A2,1)-(B2<A2)*(C2-D2)-MEDIAN(B2,C2,D2)+MEDIAN(A2,C2,D2)

assumes that C2 to D2 will always cross midnight

thanks... got it.

Hi Guys wondering if you could help me

i need to create a time sheet where by hours from 7 am to 18:00(6pm) get placed in one area and then hours after this get placed in another area to work out how many hours worked on on morning shift and then in the evening. as sometimes we only work 2 hours between 7-18 and then 8 after that your help would be greatly appreciated

TimeSheet.xls

Create a new thread, do not hijack someone else's.

ok was just posting it here as my issue relates and this one appears to be solved made more sense to me

Premium.xlsx try this file

may be like this
=(D2-C2+1)*24