Figuring out time logged in during specific times

[Andrew_Harris]

I have the login/logout data (in the "Login" field in sample file. The actual file I am working on has ~4000 logins) and in the other tab ("ExtraTime") has the beginnings and ends of time periods which I need to compare against the login data.

What I need is a way to figure out how much of the logged in time segment, if any, is part of the ExtraTime logins (I hope that makes sense). I realize this should be easy, but I can't quite figure this out.

Any help would be super appreciated.

[dilipandey]

Hi Andrew,

I started with using If / vlookup function and came up with below one

Please Login or Register  to view this content.

since it appears to be working, hence don't want to improve this though I know better formulas can be used here

See attached: sample file.xlsx

Used two columns, colored in orange, to support the calculation and avoid arrays

regards,
DILIPandey

<click on below 'star' if this helps>

[Andrew_Harris]

Thanks for the response. Unfortunately, it does not appear that this solution works due to there being multiple Extra Time time periods on some dates and some of the time periods span multiple dates.

[Andrew_Harris]

Anyone else have any ideas? I normally use array formulas to deal with multiple value lookups, but with over 4000 entries that really isnt going to work (as in, it crashed excel when I tried).

[daddylonglegs]

Try this "array formula" in E2

=SUM(IF(ExtraTime!A$1:A$100>D2,"",IF(ExtraTime!B$1:B$100<C2,"",IF(ExtraTime!B$1:B$100>D2,D2,ExtraTime!B$1:B$100)-IF(ExtraTime!A$1:A$100<C2,C2,ExtraTime!A$1:A$100))))

confirmed with CTRL+SHIFT+ENTER and copied down column, see attached

[Andrew_Harris]

That seems to work, thanks!

Apparently I need to learn how to write my array functions better.

Would anyone perhaps have a link to a good location for me to learn something beyond the basics of array formulas?

Thanks

[dilipandey]

Hi Andrew,

See the below link from Microsoft :-

http://office.microsoft.com/en-us/ex...010228458.aspx

regards,
DILIPandey

<click on below 'star' if this helps>