Time calcultion formula

[DALE1612]

Hello Group, I am tying my self up in knots with trying to get a formula to respond correctly. The basis of the formula is part of a time sheet, but not your standard fixed hours timesheet. this one is based on flexi time. I have attach the xls to enable you to see what I am trying to achieve.

There are further notes on the worksheet , a key to the working / test row and a series of start and finish times. now depending on how the cell "K16" is formmated will depend on the correct answer displayed ( this I understand) but when different start and finish times are entered, sometimes the cell result reads correct and other time incorrect. I have to change the cell "K16" from time to number and visa versa.

I have been using a series of if statements to get the correct result without total success and would greatly appreciate some guidance from you experts.

Many thanks..

 

[NoS]

I'm no expert but seeing nobody making suggestions, I'll give it a shot.

Not sure all your pop up comments in column K are correct (rows 18 and 19) and the example data is all nicely confined to the same day, never crossing midnight, at which time this will fail.
Try this in K13 and drag down.

=IF(AND(F13<=NST,I13>=NFT),9/24,IF(I13<=NST,0,IF(F13>=NFT,0,IF(AND(F13<NST,AND(I13>NST,I13<=NFT)),I13-NST,IF(AND(F13>NST,I13>NFT),NFT-F13,IF(AND(F13>=NST,I13<=NFT),I13-F13,"Problem"))))))*24

 

[Pecoflyer]

I'rm completely at a loss as to your example. What is RTB? Which are the expected results?

 

[p45cal]

K13:
=IF(COUNT(F13,I13)=2,MAX(0,MIN(NFT,I13)-MAX(NST,F13)),"")

custom number format:
[h]:mm

copy down.

Will any times cross midnight?

 

[DALE1612]

Thanks P45cal for getting back to with an working formula. In answer to your question The normal work time would not exceed 9hrs, but when overtime is worked then there could be the possibility that the time would fall over the 24th hour, but this would be a much more complicated calculation which would be displayed in a different colunm, as there are more variables to take into consideration.

I have to appologise for not getting all the info together first. I meant to inform you that the finished time needs to be a decimal value. I have tried making some alteration to your formula but it did not produce the correct result.

 

[p45cal]

Decimal hours:
=IF(COUNT(F13,I13)=2,MAX(0,MIN(NFT,I13)-MAX(NST,F13))*24,"")
format as you will but not a time/date format.

 

[DALE1612]

Many thanks P45cal. I had input the multiplier earlier today, at the end of the formula but the result was jibberish. I had forgoten to change the cell format. All is well now..

Thanks
Dale