• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to get nearest deadline


Well-Known Member
To get the nearest time (and date) in the future from NOW(), in any cell in your sheet this array-entered formula:
(array-entered means Ctrl+Shift+Enter while committing the formula to the sheet, NOT just Enter)
Last edited:

Peter Bartholomew

Well-Known Member
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.


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

... 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

Well-Known Member
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.