This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am helping the owner create a working database. In his excel spreadsheet he is attempting to make a working clock that automatically calculates the time. To make it as user friendly as possible for all employees. We need this clock to advance the same as a computer clock without having to recalculate. Is this function allowed in excel or any office product?
For example...employee X starts a job...we just want the employee to be able to see the clock in cell and enter that time in their section on spreadsheet. Or even better to have an icon to click that says start/end time that would automatically fill in the correct time and date.
Most employees are not very computer savvy so the easiest possible solution would be best.
Thanks for the help.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi,
You can have a clock in Excel that updates in real time but this requires VB code and I would suggest that it would be very error prone for these users to look at this clock and enter an equivalent time in another cell.
You can get the current time in a cell by pressing
CTRL+Shift+:
Is that beyond your users?
This is another situation where circular references are very useful.
When you say non recalculation, do you really mean that the even when nothing is happening to the sheet, the clock will just show continuous current time? This is doable with a continuous loop. I have not tried it, but suspect Excel may become unresponsive and fully preoccupied with updating the clock.
If you want to get an update every time there is an event, then this can be very easily done.
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | FALSE | user1 | = IF($B$3,0,IF(C3<>G3,2,0)) | =IF($B$3,0,MIN(D3,E3+1)) | =IF($B$3,0,IF(E3=1,F3+1,F3)) | =IF($B$3,0,IF(E3=1,C3,G3)) |
| 2 | Start time | =IF(E3=1,C5,C4) | = IF($B$3,0,IF(C5<>G4,2,0)) | =IF($B$3,0,MIN(D4,E4+1)) | =IF($B$3,0,IF(E4=1,C5,G4)) | |
| 3 | Now | =TEXT(NOW(),"hh:mm:ss") | ||||
FILE>OPTIONS>FORMULAS>calculation options>enable iterations needs to be selected.
B1 is a reset, TRUE to reset, FALSE to run
C2 is the only input, a new user signs on (perhaps from another sheet, and times in C2 and C3 can report to that same sheet too). So this can become =sheet1!A1 if the user inputs the name there for example.
Every time a new user signs on, there is a new start time in B2. Every time there is an input or a change anywhere in the workbook, or it is opened or saved (provided the workbook is set to auto recalc), the current time in C3 updates itself.
This entire sheet can be hidden and locked away in the background.
Far more typing than using VBA clearly, but an alternative if there is a need to not use VBA.
Nice Shortcut!
But Without Date
I Want a
Sunday, December/13/2014 3:24 PM
I would suggest searching for "Excel stopwatch", and you'll find lots of options.
However, based on the brief detail you provided, when I need times entered in cells I usually try to automate that with a button or hotkey. That way you get a valid date/time and don't have to worry about data entry errors. You can push a current time to the active cell, or a cell of your preference even more easily than setting up a real-time clock.
Put something like the following in a code module:
Sub InsertTimeForMe
Sheets("Sheet1").range("A1").value = now()
end sub
This just shows how easy it is (one line of code). You could add a button and link it to this macro.
If you want to autofill a fixed location, just change "A1". If you want to rename your sheet, just rename "Sheet1" to your target sheet name. If you want to add new date/time stamps to different cells (for example, start and end time) it is possible to add a few lines of code to increment which cell the date/time is populated to... or, you could just populate it to the activecell, although that isn't a great idea because your user could select a cell that you don't actually want overwritten.
HTH
Nice shortcut!