How to get nearest deadline

[ThrottleWorks]

Hi,

Please see attached file for details. Thanks.
Can anyone please help me in this.

 

[p45cal]

To get the nearest time (and date) in the future from NOW(), in any cell in your sheet this array-entered formula:
=MIN(A2:A11+TODAY()+IF(MOD(NOW(),1)>A2:A11,1,0))
(array-entered means Ctrl+Shift+Enter while committing the formula to the sheet, NOT just Enter)

 

[ThrottleWorks]

Hi @p45cal sir, thanks a lot for the help.
Have a nice day ahead.

 

[Peter Bartholomew]

Since your run times do not have any date information, I assume the times apply to every day. In which case I would use Name Manager to define formula 'WaitTime' that refers to

= MOD( RunTime - Now, 1 )

The next run time would be

= Now + MIN( WaitTime )

Because WaitTime is defined as a Named Formula it will evaluate correctly as an array; CSE is not needed.

 

[vletm]

ThrottleWorks
The nearest time from 'Your named now' is 19:30.

 

[vletm]

ThrottleWorks
I checked one more time ...
You asked 'The nearest time',
but later You 'maybe' meant to ask 'The nearest NEXT time' ...
Gotta make both ...

 

[Peter Bartholomew]

Mmm, might be some compatibility issues. The pre-2007 format opened on a 365 Insider Fast installation.

I think it is the next, not the nearest run time that is needed. Using the file from @vletm this would give the following:

 

[vletm]

Peter Bartholomew
Assume NOW time has already marked in cell C2.

ThrottleWorks
... still reading ... or try to read ... 'nearest deadline'
NOW it is 22:30 then nearest dealine would be 03:30 AM
Yes - No - - - now isn't 22:30 or not or it could be current time, who knows?
... then 'deadline' won't necessary to be 03:30 AM ... it current time isn't 22:30.

Please, try next time write 'what do You would like to get?'
with clear logic ...

 

[Peter Bartholomew]

@vletm
Having nicked your workbook I at least did you the courtesy of using the infrastructure you had set up ! Because I treat direct references as qualitative errors, I simply applied the name 'Now' to the cell.