• 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

    Hui...

  • 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

ThrottleWorks

Excel Ninja
Hi,

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

Attachments

  • Book1.xls
    32 KB · Views: 15
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)
 
Last edited:
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.
 
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:
 

Attachments

  • NextRun (PB).xls
    74 KB · Views: 7
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 ...
 

Attachments

  • Book1.xls
    41 KB · Views: 1
@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.
 
Back
Top