+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Using real time in spreadsheets

  1. #1
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Using real time in spreadsheets

    HI

    I am trying to produce a simple spreadsheet whereby I can monitor if staff working alone are overdue to phone in to say they have finished and are safe.

    All I need is a column for time due to finish and another column that shows time (hrs Mins) elapsed. If that could change colour after say 30 mins that would be even better!

    Clearly the data required is real time and time due to finish with a simple formula to calculate time since due to have finished but have tried loads of ideas on forums but can never get a formula to work using TIME functions. I appreciate you have to activate the sheet for real time to update.

    Any ideas please??

    Thanks for reading

    Mark

  2. #2
    Forum Moderator
    Join Date
    05-14-2009
    Location
    Overtherainbow
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    6,381

    Re: Using real time in spreadsheets

    You're welcome
    Last edited by Pepe Le Mokko; 01-05-2019 at 12:52 PM.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,676

    Re: Using real time in spreadsheets

    As Mark is a new member, we can provide the link for him this time as an example of what we expect: https://chandoo.org/forum/threads/us...ormulas.40616/

    Mark - please note that you must adhere to this forum rule in future. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Using real time in spreadsheets

    Sorry new to this! Have deleted other forum questions

    Mark

  5. #5
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Using real time in spreadsheets

    Sorry new to this! Have deleted other forum questions

    Mark

  6. #6
    Forum Expert
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    3,244

    Re: Using real time in spreadsheets

    Also posted Here https://www.mrexcel.com/forum/excel-...-formulas.html
    which has not been deleted

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,567

    Re: Using real time in spreadsheets

    It's hard to visualise what you want. So....

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,567

    Re: Using real time in spreadsheets

    Incidentally, you don't have to delete the other threads. The reason that the rule is there is 'cos it's helpful to know if you have asked others. We can then quickly check to see if you already have a working solution. That way we don't waste our time.... and don't get annoyed!!

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,480

    Re: Using real time in spreadsheets

    The formulas themselves should be simple. The NOW() function will return the current date/time serial number (well the time at the last calculate event). If you have a cell with the desired check in date/time, a simple boolean function like =(NOW()>=check in date/time) will return TRUE when the current time (time at last calculate event) is after the check in date/time.

    A few things to consider:

    1) If you are unfamiliar with how Excel stores and uses dates and times as serial numbers, I recommend reviewing those basic principles: http://www.cpearson.com/Excel/datetime.htm
    2) NOW() is a volatile function, so it will calculate with every calculate event. While you are working in Excel (editing cells and making other changes that trigger calculate events), the NOW() function will update. When you step away from Excel, the NOW() function will stop updating. One major consideration here is whether you will need to figure out something (a macro or other timed event running in Excel) to make sure calculate events are regularly and frequently triggered or if you can trust your own workings in Excel to trigger calculate events frequently and reliable enough.
    3) Note that this is asuuming all times are complete date/time serial numbers. If you are storing date and time separate, additional considerations will be needed in the formulas.

    The spreadsheet formulas/programming should be simple. The main consideration will be in whether you need something special to make sure the calculate events are frequent enough.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  10. #10
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Using real time in spreadsheets

    Hi Glenn

    Thanks for your reply.

    Have attached a non working example spread sheet where A3 would show actual NOW time. B5-B8 show manually inputted times due to finish and C5-C8 would show the result of a calculation; current time less time due to fimish, to show how long ago they finished - if more than 30 mins could be highlighted in red even better. Basically we have guys out working alone in a hazardous occupation and all I want is a way of seeing how long ago they finished. Then once they've phoned in to confirm they are OK we could delete the entry which would highlight anyone who has failed to make contact.

    Thanks

    Mark
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Using real time in spreadsheets

    Thanks - will try to look into this further later.

    Mark

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,480

    Re: Using real time in spreadsheets

    The spreadsheet programming seems straightforward.

    1) I note that your time values are time only, not date/time. In A3, I entered the formula =MOD(NOW(),1) which returns the time portion of the current date/time serial number.
    2) In C5, I entered a simple subtraction =$A$3-B5 (copy/fill down). Note the mix of relative and absolute references.
    3) Because of the way Excel will handle negative times (when A3 is before the time in B5), when the current time is before the time due to finish, Excel will display #####. When the current time in A3 is after, Excel will display the time since finishing.

    That part is easy. From there, every time you make a change to the spreadsheet or press F9 or anything else happens to trigger a calculate event, A3 will update with the current time, and the other formulas will follow. The challenge is to understand how you are going to use this so you can decide if something needs to be done to automatically trigger calculate events or if your regular use of Excel will be enough to trigger calculate events.

  13. #13
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,301

    Re: Using real time in spreadsheets

    Does the attached help and give a starting point?

    If the finish time is before midnight and the current time is afer midnight then this will need adapting and it would be better to record the Date & Time rather than just the time.
    Use G1 to vary the sensitivity and column D to change the drop down to Yes.
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

    Forum Rules Updated September 2018. Please read them by clicking here.

  14. #14
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Using real time in spreadsheets

    Thank you so much for your reply, this works exactly as I wanted. Would you be able to suggest the easiest way to make the spreadsheet self - update rather than relying on pressing F9 or manually changing something else. I've looked at a few suggestions on this and tried to use them but to be honest venturing into macros is completely new territory to me and guess I'm not inputting it correctly. If you could help I would be so grateful but would need a complete idiot guide! Many thanks again for the previous help. Mark

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    10,480

    Re: Using real time in spreadsheets

    This is not the kind of VBA programming I use, but I found these two example tutorials:

    https://analystcave.com/excel-vba-application-ontime/
    https://trumpexcel.com/vba-events/#E...A-OnTime-Event

    Both tutorials use the application.Ontime method to schedule a recalculate event: https://docs.microsoft.com/en-us/off...ication.ontime

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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