|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
09-08-2007, 07:48 PM
|
|||
|
|||
|
auto date and time on spreadsheet
I am trying to build a spreadsheet for logging in our fire hydrant tests. I am looking to write a funtion that will allow me to enter one letter or symbol in a cell and have the current date displayed. I will also need the same for time. I could also set the function to be contingent on a number value of a different cell. The function must keep the date and time static after entry. Any help would be greatly appreciated...... Mitch
|
|
#2
09-09-2007, 12:52 AM
|
|||
|
|||
|
A little more info.
Column I, cells 3-255 are my dates Column J, cells 3-255 are my times Column K, cells 3-255 are my static pressures I would like to enter a value >1 in the K column and have I and J auto fill. I am very technology challenged so feel free to talk down to me  I usually understand better that way.
|
|
#3
09-09-2007, 05:37 AM
|
||||
|
||||
|
__________________
oldchippy -------------  Click here >>> Top Excel links for beginners to Experts Forum Rules >>>Please don't forget to read these
|
|
#4
09-09-2007, 08:11 AM
|
|||
|
|||
|
Quote:
I guess I am not smart enough to pull it off. Thanks.....Mitch
|
|
#5
09-10-2007, 05:50 AM
|
||||
|
||||
|
Another quick way to enter the date and time in cells is by using short-cut keys, whether this is what you want? Will you be entering this information on the same day that the tests are carried out?
DATE: use Ctrl+; TIME: use Ctrl+Shift+; These will be fixed and will not update.
__________________
oldchippy ------------- Click here >>> Top Excel links for beginners to Experts Forum Rules >>>Please don't forget to read these
|
|
#6
09-10-2007, 08:04 AM
|
|||
|
|||
|
I will be entering the info as each hydrant is checked. We carry laptops on all of our engines. I have been using the shortcuts, but I was hoping to have the date and time cells auto fill. I am sure there was a code on the link you sent me. I just couldnt figure out how to change it to apply to my sheet. Like I said before, I dont have a lot of experience with this. Thanks again for your help.........Mitch
|
|
#7
09-10-2007, 08:16 AM
|
||||
|
||||
|
OK, well using the code from the link - post 3 from Irhodes
this is in col A =IF(B2="","",IF(A2="",NOW(),A2)) this is in col B =IF(C2="","",IF(B2="",NOW(),B2)) basicly it works like this I have a drop down box in col c when I select a company from the drop down box col a is date stamped and col b is time stamped, this has been working for almost a year now. If you apply this (alter range to suit yours) you will get a circular reference error come up, but if you then go into Tools > Options > Calculation and click Iteration, you will then get what you want. Try it.
__________________
oldchippy ------------- Click here >>> Top Excel links for beginners to Experts Forum Rules >>>Please don't forget to read these
|
|
#8
09-11-2007, 07:45 PM
|
|||
|
|||
|
I have got it working. Here is the code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("K:K"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
.Offset(0, -2).ClearContents
Else
With .Offset(0, -1)
.NumberFormat = "hh:mm:ss"
.Value = Time
End With
With .Offset(0, -2)
.NumberFormat = "mm/dd/yy"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
Last edited by VBA Noob; 03-06-2008 at 07:34 PM.
|
 |
| Bookmarks |
New topics in F1 Get the most out of Excel Formulas & Functions
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Linear Mode
