Using real time in spreadsheets

[Mark Southgate]

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

[Pepe Le Mokko]

You're welcome

[AliGW]

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.

[Mark Southgate]

Sorry new to this! Have deleted other forum questions

Mark

[Mark Southgate]

Sorry new to this! Have deleted other forum questions

Mark

[Fluff13]

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

[Glenn Kennedy]

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 Kennedy]

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

[MrShorty]

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.

[Mark Southgate]

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

[Mark Southgate]

Thanks - will try to look into this further later.

Mark

[MrShorty]

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.

[Richard Buttrey]

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.

[Mark Southgate]

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

[MrShorty]

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

[Mark Southgate]

These sound perfect, not only will it hopefully solve the problem, I will learn as well! Thank you so very much for your prompt and useful advice - thank you!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.