• 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.

Running seconds in Ctrl+Shift+colon

Access

New Member
How to get the correct seconds when i press Ctrl+Shift+colon. Currently I am getting seconds as 00. I need to take the time study for small tasks in my team. I cannot use a macro since I am using an open source spreadsheet and macros are restricted. Any help is greatly appreciated.
 
Thanks Hui. But I cannot use a macro since its restricted for me. Is there a way to get the seconds without the macro for the ctrl+shift+;
 
hi webmax.. even if i format the cells in custom it gives me 00 for seconds and not the real seconds. I was told that getting the actual seconds is not an option in Ctrl+Shift+; Wanted to know if there is a work around other than using a macro...
 
Hi ,

I do not know how useful this will be , but give it a try :

1. Put the formula =NOW() in any unused cell , say P1.

2. If you expect a particular column , say N2:N100 , to have the times , format them as hh:mm:ss , and create a Data Validation for this range , using the List option , and entering =$P$1 as the list source.

3. Any cell in the range N2:N100 will now allow only one entry viz. the time ; when you click on the drop-down arrow , you will see the time present in P1 , but when you click on it , the value stored in the cell will be the current time.

Narayan
 
Hi Narayank..Thanks for the idea! I am trying to get the time taken by capturing the start and end time for a task. I need to take quite a few number of samples. So here what happens is the start time gets refreshed to the current time when I enter the stop time due to NOW():( The start time should not get refreshed ideally.
 
Hi Narayank..Thanks for the idea! I am trying to get the time taken by capturing the start and end time for a task. I need to take quite a few number of samples. So here what happens is the start time gets refreshed to the current time when I enter the stop time due to NOW():( The start time should not get refreshed ideally.
Hi ,

I am not sure I have understood your problem ; as I posted earlier , if your times are entered in the range N2:N100 , using Data Validation , then the cell which has the formula =NOW() will certainly get refreshed whenever the worksheet recalculates.

However , the values in the range N2:N100 will not change from the time that they were selected , since what is entered in each cell is a static value of the current time.

So , if your start times and end times are in two columns , have the DV in both the columns , and enter the start times and end times by selecting using the drop-downs in each cell. Every cell should retain its selected value.

I agree that if you go back to a cell which already has a time value that was selected earlier , and you click on the DV drop-down again , a new time value will be entered in that cell.

Narayan
 
Hi,

You can try this formula by setting the Excel Calculation options to Manual. The drawback with this is, everytime you want to calculate the difference between Endtime and Start time you will have to go into each cell , edit and enter.

Also, if you keep the Start time(Sheet1) and End time(Sheet2) in different sheets (Endtime + other calculations in sheet2) and make use of the "calculate sheet" option to refresh only that sheet data. This should help you in some way to achieve the time difference.

=RIGHT(TEXT(NOW(),"mm:hh:ss"),2)
 
Thanks much NarayanK.. Apart from having to select the value from the DV drop down, the idea works like a gem.
 
Hi, Access!
I've just tested this and worked fine using a non specifically formatted cell, i.e., General format:
=SEGUNDO(AHORA()) -----> in english: =SECOND(NOW())
Does it work for you?
Regards!
 
Hi SirJB7 - Thanks for the tip. I tweaked the formula as =HOUR(NOW())&":"&MINUTE(NOW())&":"&SECOND(NOW()). However i guess this is just another way of formatting the cell, my requirement was that the captured time should not change when I enter a new time in a new cell in the same worksheet. NarayanK's DV work around works perfectly. I was curious as to why the time changes in other cells when I enter the same formula(above) in a new cell?
 
Hi, Access!
You're right, all formulas of lohithsriram, you and me are volatile, i.e. are calculated any time any cell changes or worksheet gets recalculated, since they're using the main function NOW(), which is volatile.
Lohithsriram's retrieves a string containing the seconds, mine retrieves a number containing the seconds, while yours retrieves a string with the hour in format hh:mm:ss. If you had set the formula to:
=NOW()
and the cell format to hh:mm:ss, then it'd have retrieved a number (greater or equal 0 and less than 1) representing the value of the time.
NARAYANK991 set a data validation condition to be the actual time intended to be used when validating other cell entries.
Hope it helps.
Regards!
 
Narayank, I ran into another issue, when I use the DV method. I notice that cell (A1) which has the =NOW() formula displays the value when the worksheet was refreshed lastly (eg 00:24:30).
And now when I use the drop down in the DV (referenced to A1) lets say in cell B1, the last refreshed value of of A1 (00:24:30) gets picked and not the current time.
Now since the =NOW() being volatile, A1 gets refreshed to (eg 00:26:25) when I selected the drop down in B1. Now if I do another DV in let's say B2 the last refreshed time in A1 (eg 00:26:25) gets picked.
So the actual time elapsed between B1 & B2 is not being captured. Hope I am not very confusing....
Any help is much appreciated
 
Hi ,

I think that's the way it works ; the only way out may be to press F9 before using the drop-downs ; otherwise , you can have a Worksheet_SelectionChange event procedure , which recalculates just the cell A1 , so that each time you move the cursor , A1 will reflect the current time. Since you can have just the cell A1 recalculating , it will not be too much of a drag , as compared to the entire worksheet recalculating.

Narayan
 
Back
Top