Timeline Formula

[aftabn10]

Hi, I have wrote a formula that will allow me to see how long a person has been in a particular between 07:30 to 09:30. My formula:

=IF(AND(K$5>=$C6,K$5<$D6),$D6-$C6,"L")

works for 1 field but not for anyothers and im not too sure why. If somebody could please help, would really appreciate it.

Thanks in advance.

[JBeaucaire]

The formula structure looks fine. Post a sample workbook showing the formula NOT doing what it's supposed to so we can see what your data looks like.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[aftabn10]

Thanks for your response JBeaucaire. I have attached the workbook as required.

[teylyn]

Your conditions don't make a lot of sense. The way they're defined now, you will only get a value different from "L" if a time in C starts before the current column and ends after the current column. Is that what you want? Hard to tell.

Can you describe in words what you would like to achieve?

[aftabn10]

teylyn, thanks for your response. Sorry, ill try to clear up what i am looking to do.

The columns between K:BK should fill in the between values from column C to D. For example if humpty dumpty is on a call from 07:30 till 08:13 then col K and L should have a value of 00:15 and col M should have a value of 00:13.

START | END | 07:30 | 07:45 | 08:00 | 08:15
07:30 | 08:13 | 00:15 | 00:15 | 00:13 | L

Hope that makes sense. Thanks once again for your help.

[teylyn]

if humpty dumpty is on a call from 07:30 till 08:13 then col K and L

7:30 is in column E.

Why would you want column K (9:00) and L (9:15) involved?

Please be as precise with your specs as you want the helpers here to be precise with their suggestions.

I suggest you start over and refer to the file you posted instead of sending us off on a wild goose chase.

[aftabn10]

teylyn, apologies meant to say E, F and G instead of K, L and M. I had deleted some columns on my version hence the confusion.

[teylyn]

confused. Upload a worksheet with the expected results filled in manually.

[aftabn10]

teylyn, sorry to confuse you. As required, i have attached the expected results sheet. Hope this makes more sense. thanks once again.

[daddylonglegs]

Try using this formula in E3 copied across and down

=MAX(0,MIN(E$2+"0:15",$D3)-MAX(E$2,$C3))

That will give a zero value in most cells - you can use a custom format like this to display that as "L"

hh:mm;;"L"

Then conditional formatting to format the zero values blue, if you want, see attached

If you want an actual "L" in the cell delivered by the formula rather than formatting then you could use this revised formula

=IF(OR($C3>=E$2+"0:15",$D3<=E$2),"L",MIN(E$2+"0:15",$D3)-MAX(E$2,$C3))

[aftabn10]

daddylonglegs, thanks, i will have a look and see if that works.