Returning Next (future) Closest Timing from Now

[Varianceplay]

Hi all,

I am making a doc to extrapolate a list of possible timings for recurring events in a game.

I have attached the file.

The main user interface is sheet "Display". The highlighted cells are pre-programmed for users to input values to update the timer.

The codings are hidden in sheet "Hardware".
I have inputted some dummy values in Hardware.
Given that the events happen on recurring known timings, the list of possible timings can be extrapolated and shown in array N:Y.

For example, possible spawn times for Lycan1 is N26:Y26.

My question is how to display in "Display" sheet, cell C12, the earliest possible future timings from N26:Y26? bearing in mind that timings next to 23:59 will jump into 00:00++

I have included the current time in "Display", Cell G3. to use as reference for the comparison.
I will eventually add a refresh button to update Now().




Thank you!

[BMV]

Please Login or Register  to view this content.

[Varianceplay]

Hi BMV, Thanks so much! Its so crazy complex, wasnt expecting that. But it works and ill just replicate it using your file.

May i check, whats the purpose of the logic check on "Hardware" N26 to P26? specifically N26 < Display! $G$8.
I dont see this logic check referenced anywhere in the formulae.

[AliGW]

Administrative Note:

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Similarly, please fill in the Office version that you are using.

Thank you for helping us to help you.

[BMV]

There are some tricks
1. the future value is next so we can find the value that less then now and add 1 position
2. If the value that less then now then we mast get first value. For this case i use MOD function
3. Because you generate values such as sum previous value and delta some values have extra day (+1)

and the formula can be easy

Please Login or Register  to view this content.

i thought the set of values is only time and started solution for that but it is not so.