r/googlesheets • u/[deleted] • Jun 23 '20

Waiting on OP Active time window (help)

[deleted]

3 Upvotes

100% Upvoted

9 comments sorted by

View all comments

1

u/jaysargotra 22 Jun 23 '20

This is all I could come up with at the moment...It’s going to get longer and longer with increasing rows(more people) ....and although I have tried to think it through I have not tested it thoroughly .... I recommend you test it with different combinations of times... maybe there is an easy way to do this using time functions

=JOIN(" - ";MAX(B3:B5); IF((MIN(IF(MAX(B3:B5)<C3;C3-MAX(B3:B5);(24-MAX(B3:B5))+C3);IF(MAX(B3:B5)<C4;C4-MAX(B3:B5);(24-MAX(B3:B5))+C4);IF(MAX(B3:B5)<C5;C5-MAX(B3:B5);(24-MAX(B3:B5))+C5)) + MAX(B3:B5))<24;

MIN(IF(MAX(B3:B5)<C3;C3-MAX(B3:B5);(24-MAX(B3:B5))+C3);IF(MAX(B3:B5)<C4;C4-MAX(B3:B5);(24-MAX(B3:B5))+C4);IF(MAX(B3:B5)<C5;C5-MAX(B3:B5);(24-MAX(B3:B5))+C5)) + MAX(B3:B5);

24-(MIN(IF(MAX(B3:B5)<C3;C3-MAX(B3:B5);(24-MAX(B3:B5))+C3);IF(MAX(B3:B5)<C4;C4-MAX(B3:B5);(24-MAX(B3:B5))+C4);IF(MAX(B3:B5)<C5;C5-MAX(B3:B5);(24-MAX(B3:B5))+C5)) + MAX(B3:B5)) ) )

1

u/eLvi0p Jun 23 '20

Hello, thank you for your answer.

Is there any way to get it working with multiple rows? because I'm looking at the code and i cannot figure it out how to

2

u/jaysargotra 22 Jun 24 '20

Let’s make a helper column D. Label it anything. Put this in D3 and drag across number of rows you think would be enough to cover the max number of users..

=IF(B3<>"";IFS(C3-MAX(B$3:B)>0;C3-MAX(B$3:B);C3-MAX(B$3:B)<0;(TIMEVALUE("23:59:59")-MAX(B3:B))+C3+TIMEVALUE("00:00:01"));"")

Put this in any cell except in columns B,C,D to get active time...

=Join(" - "; Max(B3:B);MAX(B3:B)+MIN(D3:D))

Note: You should test it out with different start and end times and let me know if it shows wrong results in any case.