+ Reply to Thread
Results 1 to 6 of 6

How to periodically compare range of time values against current time

  1. #1
    Registered User
    Join Date
    08-08-2020
    Location
    Boston
    MS-Off Ver
    2019
    Posts
    27

    How to periodically compare range of time values against current time

    Hi all and good afternoon!

    Does anyone have a method of periodically running a function checks a range of adjacent cells in a column containing time values, and then bolds the time closest to the current time. I'd like my task list which contains scheduled times to show me which task is closest to the current time of day, by bolding the time. I've read about onTime briefly, and looked at functions which run when the sheet is opened, but is there a way to have a function run once every 15 minutes on a sheet? I'm working with an already slow workbook with lots of calculations already, and I wasn't sure if there was a low impact method to do this calculation-wise, or that wasn't polling constantly?

    Best,
    Frank

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,140

    Re: How to periodically compare range of time values against current time

    How do you plan on running this? Do you want to open the workbook as you need it and then look at the schedule, or do you want to keep the workbook open all the time? The latter will eat up resources and may cause other problems.

    Another solution would be to use the Windows Task Scheduler to fire off the workbook, get the schedule and email (or text) it to you. Perhaps this could be modified only to mail to warn you of tasks coming up in the next 15 minutes.

    Personally, I would look for a third party reminder service.

    Also, a sample workbook would help a lot.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-08-2020
    Location
    Boston
    MS-Off Ver
    2019
    Posts
    27

    Re: How to periodically compare range of time values against current time

    Hi sorry I missed your reply, I thought my post had fallen into the post abyss, but you responded, Thanks!

    The sheet will be open most of the day, from first use of laptop in the morning through about 6pm.

    You mention "do you want to keep the workbook open all the time? The latter will eat up resources and may cause other problems."
    I have noticed that my sheet, which has many formulas and functions in it as it is a rather complex combined to do list, project management app, and book keeping app, will run procedures early in the day in 2 seconds time, then later in the day the same calls will take 12 seconds! Do you have any insight into why based on your comment? If I close and re-open excel those procedures go back to running at 2 seconds time, so I know it's not Windows 10...

    With the workbook being open most of the time, I wonder if there might be an internal solution to Excel which I could use, for example, maybe it would not introduce much latency to add a simple comparison of NOW to the likely 20 maximum rows of task associated times for today, and to highlight the one which matches NOW most closely. I've already got many selection change events so there is some small lag moving from cell to cell, but it's not distracting yet. If I could add another test like this, maybe I could get away with it.

    Anyone have any thoughts on a strategy like that?

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,140

    Re: How to periodically compare range of time values against current time

    In the past, I tried using an Excel application to check a mailbox and send out a notice if mail arrived in a specific folder. One problem was that if I had another Excel application open at the time, when the mail app closed down, it also attempted to close down the app I was actively using. Also consider that if the file is 1 MB in size, it will occupy 1 MB RAM (unless you have so much else going that it gets swapped out). Nowadays when RAM is measured in gigabytes, this isn't as much of a big deal as it was in the past.

    I eventually solved my problem with a VBA application in Outlook.

    What I suggest you do is use the windows task scheduler to launch the Excel file on a schedule. I have also done this in the past. There is a slight issue to this. The windows task scheduler can open a file, but it cannot run a macro. One way around this is to put the macro you want to run in an On Open event in the file. This is inconvenient if you have to work with the file a lot to update the schedule for example. If you open the file, the macro will run. What I did was to make this a three-step process. The task scheduler opens an Excel "script" file. The script file has an on open event and it runs the macro in the target spreadsheet.

    Somewhere in my archive, I have the documentation and the code to do this. I will have to find it. I also have an application that can mail the results. This application I do have handy.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,140

    Re: How to periodically compare range of time values against current time

    I found it and it is attached. I suggest you do a web search for how to use the Task Scheduler. You'll probably find a better explanation than I can give, but if you need help, I'll do what I can. I need to mention one other thing: Both the script file and target files need either to be trusted documents or in trusted folders. They do not need to be in the same folder. When I did this, I was running dozens of reports so I had a script folder and a report folder.

    For anyone interested in the code, here it is.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,140

    Re: How to periodically compare range of time values against current time

    I promised you some mail code. Here it is. Pass the to, subject and body to this macro. If you need help stitching this together in VBA, let me know.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-23-2019, 11:09 AM
  2. Average values if the associated time of occurence falls within a certain time range
    By boarderbrent91 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-02-2014, 01:49 PM
  3. [SOLVED] Counting values in a time range - when the time range crosses midnight
    By dlocos in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-24-2014, 04:27 AM
  4. Create a Time Range off Time Stamps, and Count Intersecting values
    By athyeh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2014, 05:07 PM
  5. [SOLVED] Need to compare multiple values including TIME between, and compare rate
    By fourmurphys in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-21-2014, 04:37 PM
  6. compare time values - current time with time in cell range?
    By wyattea in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-13-2013, 11:28 PM
  7. Replies: 4
    Last Post: 10-08-2010, 04:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1