MrExcel Publishing
Your One Stop for Excel Tips & Solutions

The "NOW()" function


Posted by Gordon Henderson on January 15, 2002 12:38 PM

How can I date and timestamp a spreadsheet so that it shows the date and time of creation, but is not changed subsequently? Basically, a one-time NOW() function.


Posted by Andy Gee on January 15, 2002 12:46 PM

I use a validation list for this ie.
In cell c1 I have =Now()
In cell a1 I have a validation list with cell c1 as the source.
Each time I use the sheet, I click the drop down and select the value.

Posted by Aladin Akyurek on January 15, 2002 4:04 PM

to work.

Andy: How did you discover it?

Aladin

=====

Posted by Ivan F Moala on January 15, 2002 8:37 PM

Hi Andy

Thats a greeat Non VBA method.......
I'll keep that in mind.

Ivan

Posted by Andy Gee on January 16, 2002 3:46 AM

It just came to me in a flash of inspiration!

Posted by Chris on January 16, 2002 5:42 AM

Here is a SAMPLE I did...Don't know exactly what you are wanting, but maybe you can see what you need by looking at this example of mine:


Dim strFileName As String
Dim strNowDate As String
Dim lngMonth As Long
Dim lngDay As Long
Dim lngYear As Long
Dim strCharMonth1 As String
Dim strCharMonth2 As String
Dim strCharDay1 As String
Dim strCharDay2 As String
Dim strCharYear As String
Dim lngFileDateMonth As Long
Dim lngFileDateDay As Long
Dim lngFileDateYear As Long
Dim intCountMonth As Integer
Dim intCountDay As Integer
Dim intLoop1 As Integer
Dim intLoop2 As Integer
Dim intLoop3 As Integer
Dim strCurrentOpenedWorkBook As String

'extracts today's date as serial number
strNowDate = Now()
'converts serial number back to months
lngMonth = Month(strNowDate)
'converts serial number back to days
lngDay = Day(strNowDate)
'converts serial number back to years
lngYear = Year(strNowDate)

'assign a file name from date
strFileName = "M1138-" & lngMonth & "-" & lngDay & "-" & lngYear

'assign current workbookname to varible
strCurrentOpenedWorkBook = ActiveWorkbook.Name

'change directories
ChDir "C:\qsi"

If strCurrentOpenedWorkBook = "M1138.xls" Then
GoTo SaveAs
Else
If strCurrentOpenedWorkBook = Dir(strFileName & ".xls", _
vbNormal Or vbReadOnly Or vbHidden Or vbSystem Or vbArchive) Then
'open an existing file
Workbooks.Open Filename:="C:\qsi\" & strFileName, UpdateLinks:=1

'turn off user prompts
Application.DisplayAlerts = False

'close template file
Windows("M1138.xls").Activate
ActiveWorkbook.Close

'turn on user prompts
Application.DisplayAlerts = True

GoTo Done
End If

SaveAs:

'checking all LETTER cases
If strCurrentOpenedWorkBook = "M1138.xls" Then

ChDir "C:\qsi"
If strFileName & ".xls" = Dir(strFileName & ".xls", _
vbNormal Or vbReadOnly Or vbHidden Or vbSystem Or vbArchive) Then

'turn off user prompts
Application.DisplayAlerts = False

'open an existing file
Workbooks.Open Filename:="C:\qsi\" & strFileName, UpdateLinks:=1
Application.Run Macro:="Auto"

'close template file
Windows("M1138.xls").Activate
ActiveWorkbook.Close

'turn on user prompts
Application.DisplayAlerts = True

GoTo Done
Else
'turn off user prompts
Application.DisplayAlerts = False

'save a new day under new file name
ActiveWorkbook.SaveAs Filename:="C:\qsi\" & strFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False

'turn on user prompts
Application.DisplayAlerts = True
GoTo Done
End If
End If

If strCurrentOpenedWorkBook <> Dir(strFileName & ".xls", _
vbNormal Or vbReadOnly Or vbHidden Or vbSystem Or vbArchive) Then

'parse the existing file name so we can compare what we have already
For intLoop1 = 1 To Len(strCurrentOpenedWorkBook)
If Mid$(strCurrentOpenedWorkBook, intLoop1, 1) = "-" Then
strCharMonth1 = Mid$(strCurrentOpenedWorkBook, intLoop1 + 1, 1)
strCharMonth2 = Mid$(strCurrentOpenedWorkBook, intLoop1 + 2, 1)
intLoop1 = intLoop1 + 1
Exit For
End If

Next

For intLoop2 = intLoop1 To Len(strCurrentOpenedWorkBook)
If Mid$(strCurrentOpenedWorkBook, intLoop2, 1) = "-" Then
strCharDay1 = Mid$(strCurrentOpenedWorkBook, intLoop2 + 1, 1)
strCharDay2 = Mid$(strCurrentOpenedWorkBook, intLoop2 + 2, 1)
intLoop2 = intLoop2 + 1
Exit For
End If
Next

For intLoop3 = intLoop2 To Len(strCurrentOpenedWorkBook)
If Mid$(strCurrentOpenedWorkBook, intLoop3, 1) = "-" Then
strCharYear = Mid$(strCurrentOpenedWorkBook, intLoop3 + 1, 4)
Exit For
End If
Next

If strCharMonth2 = "-" Then
lngFileDateMonth = strCharMonth1
Else
lngFileDateMonth = (strCharMonth1 * 10) + strCharMonth1
End If

If strCharDay2 = "-" Then
lngFileDateDay = strCharDay1
Else
lngFileDateDay = (strCharDay1 * 10) + strCharDay2
End If

lngFileDateYear = strCharYear

'compare existing file to today's date
If lngFileDateYear < lngYear Or lngFileDateMonth < lngMonth Or _
lngFileDateDay < lngDay Then

'do not start logging macro because OLD spreadsheet was opened
Application.Run Macro:="NoLogging"
GoTo NoMacros
End If
End If
End If

Done:
Application.Run Macro:="Auto"
NoMacros:
End Sub