Free Microsoft Excel 2013 Quick Reference

Display last update date time Results

Hi all

I want to display on a spreadsheet, "last update date & time"; Is there a function to do it??

Many thanks in advance
Moony

hi, i want to know whether there is any formula for displaying the last date & time of the file saved.

Is there a way in which i can set-up excel so that it can automatically display in a cell the last date the document was updated?

I have 30 sheets and a summary sheet. On each of the 30 sheets there is a last updated box and there is also one for the whole document on the summary sheet. I would like to set up excel so that when you change anything on any of the 30 sub sheets, it automatically changes the last update box on the summary sheet.

Is any of this possible?

Does my explanation make any sense?

Only time will tell..... :-)

Hello,

I have a button on my sheet and when the user presses it I want my Label to display the current Date & Time. This will allow the user to
a) know when the sheet was last updated and
b) communicate that the button was pushed and the data was updated successfuly.

I know this is super easy but don't know the exact code, here is what I have included in my command button click subroutine.....

What's incorrect with this?    And also, how can I modify the date & time formatting, or where can I learn how to do
that?

Thank you so much,
Robert

I have searched the forum and can't find anything that solves my problem. I have a spreadsheet which is linked to several password protected spreadsheets. I have a macro which updates the links but I would like the macro to include the date that the links were last updated. Is there a code I can insert into the macro which will display the date the links were last updated?

Thanks

I am trying to get a single cell to display the following:

Last Updated: 3/18/2008 15:08 (GMT+2)

Entering =NOW() in a cell displays the date and time as required.

But entering ="Last Updated: "&NOW()&" (GMT+2)" displays the date and time as a serial number. Formatting the cell to Date does not change the serial number to date and time format.

The only way I have found to get the desired result is to use =NOW() in another cell (F13), format that cell to general to get the date/time serial number, then use ="Last Updated: "&TEXT(F13,"m/d/yyyy h:mm ")&" (GMT+2)" in the required destination cell.

As I said this works, but it strikes me as an inefficient method. Is there a formula I can enter or formatting I can apply to get the desired result without using an addition cell?

Note: The result needs to be in a single cell. Splitting text and date/time into 3 adjacent cells will not work with my worksheet setup.

Thanks.

I created a function in Excel to display the "Last Saved Date and Time" in a
cell. The code is as follows:

<
Function LastSaved( )
LastSaved = ActiveWorkbook.BuiltinDocumentProperties.Item(12)
End Function
>

After saving the modified file, the cell displays the "Last Saved Date and
Time". I tried to modify the file another time and saved it. The cell however
does not change to the new date and time. But this was reflected under the
File -> Properties -> Statistics.

How can I update the cell to continuous display the right date and time
whenever the "Save" button is clicked ?

I hope someone can assist me?

I am looking for a solution to an excel query thats been puzzling me for a few days now. This is what im looking to do?

I have an online quote system, which my users use to create/update customer quotes. Each time they update or create a new quote, this is date and time-stamped. It has a sql/access back-end, so the updates are stored within another table.

I want to be able to create a chart/pivot table, which will display the updates for each user within the last 30 days. I can pull the data from Access, so i have two columns with username and update date/time.

I have managed to get all updates by all users displayed in a chart, but I just want to see the updates for the last 30 days.

Is it a formula that i need to create?

Any help/pointers would be very much appreciated!

I have some reports that pull data from Oracle into Pivot Tables. Refreshing the data takes about 15 minutes because the queries are complex.

Anyway these are shared reports and typically you only need to refresh them every few hours. Therefore if someone refreshes and saves it I wouldn't need to refresh if I opened it an hour or two later.

Problem is, people don't know when the data was last refreshed so wind up doing it everytime they open the book.

I was looking for an idea to eliminate this problem. What I thought was having the date/time displayed in a cell near the top showing the last time the data was refreshed. Is there a way to do this without using a Macro or perhaps another solution?

Thanks

I would like to add a cell that shows the date and time that the spreadsheet was last edited. All of the tutorials that I have read on this subject use the "now()" function, but that makes the cell update the instant the spreadsheet is opened, instead of displaying the date/time that an actual change was made.

When a spreadsheet is first opened, I would like that cell to show the date/time of when it was previously edited (or saved), and not change until I begin making changes to the spreadsheet (or save it...either functionality would work fine).

Any help would be appreciated. Thanks,

Steve

Does anyone know a formula that displays the last time a worksheet was saved. Just within the sheet somewhere, i need to display the last update.

Thanks in advance

Dave

Hi, Complete excel novice, so step by step instruction required.

I have 4 worksheets each with headings and titles in the first 7 rows. I require automatically updated last modified information displayed in cells B3, B4, B5: these are title in column "A" as follows

A3 revision number
A4 date and time
A5 author

I only need it to update if the Worksheet was modified starting in row 8 (needs to be last modified, not last saved information).

E.G. if I modify worksheet 2 for the first time on a monday at 2.30pm

the revision number in B3 should move up an increment of 1 and now display: 1
the date and time display: 20-Feb-2012 14:30
the author changes to: JFJS

If a friend modifies information in worksheet 1 on a tuesday at 11.34am
the same process of events happen and the cells fill out in worksheet 1. Worksheet 2 should not be affected because nothing has changed.

Then another friend modifies worksheet 2 again on thursday at 4.46pm
the revision number in B3 moves up an increment of 1 and now displays: 2
the date and time display: 23-Feb-2012 16:46
the author changes to: ANOTHER

Hi

I have a workbook which has a macro to loop round refreshing its data every half an hour and each time the refresh is performed the file gets saved. This file is opened by one user in the morning, and left open with the macro running to carry out the refreshing throughout the day.

This workbook is then also opened by a number of users as read only who interrogate (but do not update) the data in the spreadsheet as part of their work. Periodically, they close and re-open their copy of the workbook to ensure they have the most up-to-date version open.

To let them know when a new version is available (i.e. when the refresh has been performed and the file re-saved), I have some code which compares the last saved date/time of the file they are using with the date and time of the file they loaded the file from, and displays a message when they values are different.

I have, however, been unable to develop a macro which properly 're-loads' the workbook.

I have been able to get the file closed and the latest version opened (through a 'ThisWorkbook.Open' statement). However, when the new version susbsequently opens, none of the open events are triggered, nor is the Auto_Open macro executed. This is a major problem, as the spreadsheet relies on this processing being performed for the file to properly work. (I know that macros are enabled (I get the 'enable macros' prompt in the course of the updated version being opened, and can run macros once the file has been opened).)

Can anyone offer a solution?

Many Thanks

I am using Excel for data entry of a printed form. The data in the form is NOT copied to a database. When the form is opened I would like it to display the current date and time and have the date/time remain static and NOT updated when the form is changed or saved. I currently am using the =now() but this date/time changes.

In addition to this static date/time I would like to know how I can convert it to a different date/time based on a specified number of hours (i.e. convert USA time to Europe time).

Lastly I would like a custom date/time format and need help in creating it.

MMM DD, YYYY HH:MM am/pm

For example: Jul 6, 2005 1:04pm

Thanks, Darleen
P/S I have purchased your Excel training course so hopefully my knowledge will improve!

here is what i wanna do

I want a main sheet to display information on when other workbooks where last updated

So in Cell "A15" in workbook called "MAIN" I want it do display the date and time of the last update made in workbook named "Red Deer"

can this be done?

Try this...
Option Explicit
'

'/================================================/
Sub Pivot_Properties()
'Creates a worksheet within the current workbook
' listing pivot table information
'Creates a comment on each pivot table containing an
' abbreviated version of that information
Dim aryHiddensheets()
Dim blnColFields As Boolean, blnShowValues As Boolean
Dim blnMakeComment As Boolean
Dim d As Double, c As Double
Dim i As Long, z As Long, iPtCount As Long
Dim x As Long, y As Long, w As Long
Dim iFieldsCount As Long
Dim iRow As Long, iColumn As Long
Dim iWorksheets As Long
Dim objCalcItem As Object
Dim objCubeFld As Object
Dim objPvtField As Object
Dim objOutputArea As Object
Dim objSheet As Object
Dim strAnswer As String, strComment As String
Dim strResultsTableName As String
Dim varAnswer As Variant
Dim varPvtField As Variant, varPivotItem As Variant

On Error Resume Next

'/- - - - Variables - - - - - - - -
strResultsTableName = "PivotTableProperties"
strAnswer = ""
strComment = ""
iRow = 1
iColumn = -2
iPtCount = 0
blnColFields = True
blnShowValues = True
blnMakeComment = False
'/- - - - End Variables - - - - - -

varAnswer = _
MsgBox("Show Selected Values for each field?" & _
vbCr & vbCr & _
"Select 'No' to only show Heading names", _
vbInformation + vbYesNoCancel + vbDefaultButton2, _
"Show Values for each field...")

If varAnswer = vbNo Then
blnShowValues = False
End If

If varAnswer = vbCancel Then
MsgBox "This process has been canceled.", _
vbInformation + vbOKOnly, "Warning..."
Exit Sub
End If

'check for an active workbook
'no workbooks open, so create one
If ActiveWorkbook Is Nothing Then
Workbooks.Add
End If

'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count

'redim array
ReDim aryHiddensheets(1 To iWorksheets)

x = 0
y = 0
For Each objSheet In ActiveWorkbook.Sheets
y = y + 1
If objSheet.Visible True Then
x = x + 1
aryHiddensheets(x) = objSheet.name
objSheet.Visible = True
End If
Next objSheet

'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If UCase(Worksheets(x).name) = _
UCase(strResultsTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
'turn warning messages off
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
'turn warning messages on
Application.DisplayAlerts = True
Exit For
End If
Next

'Add new worksheet at end of workbook
' where results will be located
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.name = strResultsTableName
ActiveWorkbook.ActiveSheet.Range("A1").value = _
"Pivot Table Information"
ActiveWorkbook.ActiveSheet.Range("A1").Font.Bold
ActiveWorkbook.ActiveSheet.Range("A1").Font.Size = 16
ActiveWorkbook.ActiveSheet.Range("A1").Font.Underl ine = _
xlUnderlineStyleSingle

iWorksheets = ActiveWorkbook.Sheets.Count

Set objOutputArea = _
ActiveWorkbook.Sheets(strResultsTableName).Range(" A1")
iRow = iRow + 1

'Go through one Worksheet at a time
For x = 1 To iWorksheets
'Go to Next Worksheet
Worksheets(x).Activate
'Initialize formula and text/value count variables
i = ActiveSheet.PivotTables.Count
iPtCount = iPtCount + i
strComment = ""
If i > 0 And _
UCase(ActiveSheet.name) _
UCase(strResultsTableName) Then
blnMakeComment = True
With ActiveSheet
For z = 1 To i
strComment = ""
iColumn = iColumn + 2
ActiveWorkbook.Sheets(strResultsTableName). _
Columns(iColumn + 1) _
.NumberFormat = "@"

With .PivotTables(z)
objOutputArea.Offset(iRow, iColumn) = _
"Pivot Table Name: " & .name
objOutputArea.Offset(iRow, iColumn).Font.Size = 12
objOutputArea.Offset(iRow, _
iColumn).Font.Underline = _
xlUnderlineStyleSingle
objOutputArea.Offset(iRow, iColumn).Font.Bold
iRow = iRow + 1
strComment = strComment & "Pivot Table Name: " & _
.name & Chr(10)

objOutputArea.Hyperlinks.Add _
Anchor:=objOutputArea.Offset(iRow, iColumn), _
Address:=ActiveWorkbook.FullName, _
TextToDisplay:="Location/Name (Workbook): " & _
ActiveWorkbook.FullName
iRow = iRow + 1

objOutputArea.Hyperlinks.Add _
Anchor:=objOutputArea.Offset(iRow, iColumn), _
Address:=ActiveWorkbook.FullName, _
SubAddress:= _
Left(.SourceData, InStr(.SourceData, "!") - 1) & _
"!" & _
Range_RC2A1(Right(.SourceData, Len(.SourceData) - _
InStr(.SourceData, "!"))), _
TextToDisplay:= _
"Data Source of Pivot Table (Worksheet): " & _
Left(.SourceData, _
InStr(.SourceData, "!") - 1) & "!" & _
Range_RC2A1(Right(.SourceData, Len(.SourceData) - _
InStr(.SourceData, "!")))
iRow = iRow + 1
strComment = strComment & _
"Data Source of Pivot Table (Worksheet): " & _
Left(.SourceData, InStr(.SourceData, _
"!") - 1) & "!" & _
Range_RC2A1(Right(.SourceData, Len(.SourceData) - _
InStr(.SourceData, "!"))) & Chr(10)

objOutputArea.Offset(iRow, iColumn) = _
"Data Source - CacheIndex = " & .CacheIndex
iRow = iRow + 1
strComment = strComment & _
"Data Source - CacheIndex = " & _
.CacheIndex & Chr(10)

objOutputArea.Hyperlinks.Add _
Anchor:=objOutputArea.Offset(iRow, iColumn), _
Address:=ActiveWorkbook.FullName, _
SubAddress:=Chr(39) & ActiveSheet.name & _
Chr(39) & "!" & _
.TableRange2.Address, _
TextToDisplay:= _
"Pivot Table Location (Worksheet): " & _
ActiveSheet.name & "!" & _
.TableRange2.Address
iRow = iRow + 1
strComment = strComment & _
"Pivot Table Location (Worksheet): " & _
ActiveSheet.name & "!" & _
.TableRange2.Address & Chr(10) & Chr(10) & Chr(10)

objOutputArea.Offset(iRow, iColumn) = _
"Row Information - Order (#)"
objOutputArea.Offset(iRow, iColumn).Font.Bold
iRow = iRow + 1

objOutputArea.Offset(iRow, iColumn) = _
"Row Heading Field(s): "
iRow = iRow + 1
For Each varPvtField In .RowFields
For w = 1 To .RowFields.Count
If varPvtField.name = .RowFields.Item(w) Then
objOutputArea.Offset(iRow, iColumn) = _
" - " & " ( " & _
varPvtField.Position & " ) " & _
varPvtField.name
End If
Next w

c = 0
If varPvtField.name = "Data" Then
If .ColumnFields.Count = 0 Then
blnColFields = False
End If
If .RowFields.Count = 1 Then
objOutputArea.Offset(iRow, iColumn) = _
" - " & varPvtField.name & _
" *** [No Row Fields Selected]"
Else
objOutputArea.Offset(iRow, iColumn) = _
" - " & varPvtField.name
End If
End If
iRow = iRow + 1

For Each varPivotItem In .PivotFields( _
varPvtField.name).PivotItems
If varPivotItem.Visible Then
If blnShowValues = True Then
If c = 0 Then
objOutputArea.Offset(iRow, iColumn) = _
" Selected - " & _
varPivotItem.name
Else
objOutputArea.Offset(iRow, iColumn) = _
" - " & _
varPivotItem.name
End If
iRow = iRow + 1
End If
c = 1
End If
Next varPivotItem

Next varPvtField
If .RowGrand = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Row Grand Total is ON"
Else
objOutputArea.Offset(iRow, iColumn) = _
"Row Grand Total is OFF"
End If
iRow = iRow + 2

objOutputArea.Offset(iRow, iColumn) = _
"Column Information - Order (#)"
iRow = iRow + 1

objOutputArea.Offset(iRow, iColumn) = _
"Column Heading Field(s): "
iRow = iRow + 1
For Each varPvtField In .ColumnFields
c = 0
objOutputArea.Offset(iRow, iColumn) = _
" - " & " ( " & _
varPvtField.Position & " ) " & _
varPvtField.name
iRow = iRow + 1

For Each varPivotItem In _
.PivotFields(varPvtField.name).PivotItems
If varPivotItem.Visible Then
If blnShowValues = True Then
If c = 0 Then
objOutputArea.Offset(iRow, iColumn) = _
" Selected - " & _
varPivotItem.name
Else
objOutputArea.Offset(iRow, iColumn) = _
" - " & _
varPivotItem.name
End If
iRow = iRow + 1
End If
c = 1
End If
Next varPivotItem

Next varPvtField
If blnColFields = False Then
iRow = iRow - 1
objOutputArea.Offset(iRow, iColumn) = _
" - Data" & _
" *** [No Column Fields Selected]"
blnColFields = True
iRow = iRow + 1
End If

If .ColumnGrand = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Column Grand Total is ON"
Else
objOutputArea.Offset(iRow, iColumn) = _
"Column Grand Total is OFF"
End If
iRow = iRow + 2

objOutputArea.Offset(iRow, iColumn) = _
"Data Field(s) - "
iRow = iRow + 1
For Each varPvtField In .DataFields
objOutputArea.Offset(iRow, iColumn) = _
" - " & varPvtField.name
iRow = iRow + 1
Next varPvtField
iRow = iRow + 1

If .PivotFields.Count 0 Then
objOutputArea.Offset(iRow, iColumn) = _
"Calculated Items - "
iRow = iRow + 1

iFieldsCount = .PivotFields.Count

For w = 1 To iFieldsCount
For Each objCalcItem In _
.PivotFields(w).CalculatedItems
objOutputArea.Offset(iRow, iColumn) = _
" - Calculation Name: " & _
objCalcItem.name
iRow = iRow + 1
objOutputArea.Offset(iRow, iColumn) = _
" - Field Name: " & _
.PivotFields(w).name
iRow = iRow + 1
objOutputArea.Offset(iRow, iColumn) = _
" - Formula: " & _
objCalcItem.Formula
iRow = iRow + 1
objOutputArea.Offset(iRow, iColumn) = _
" - Solve Order: " & _
.PivotFormulas(objCalcItem.name).Index
iRow = iRow + 1

Next objCalcItem
Next w
End If
iRow = iRow + 1

If .CalculatedFields.Count 0 Then
objOutputArea.Offset(iRow, iColumn) = _
"Calculated Fields - "
iRow = iRow + 1

iFieldsCount = .CalculatedFields.Count

For Each objCalcItem In .CalculatedFields
objOutputArea.Offset(iRow, iColumn) = _
" - Calculation Name: " & _
objCalcItem.name
iRow = iRow + 1
objOutputArea.Offset(iRow, iColumn) = _
" - Formula: " & _
objCalcItem.Formula
iRow = iRow + 1
Next objCalcItem
End If
iRow = iRow + 1

If .PageFields.Count 0 Then
objOutputArea.Offset(iRow, iColumn) = _
"Page Name(s): "
iRow = iRow + 1
For Each varPvtField In .PageFields
objOutputArea.Offset(iRow, iColumn) = _
" - " & varPvtField.name
iRow = iRow + 1

objOutputArea.Offset(iRow, iColumn) = _
" Show - " & _
.PivotFields(varPvtField.name). _
CurrentPage
iRow = iRow + 1
c = 1
Next varPvtField
iRow = iRow + 1
End If

If .CubeFields.Count 0 Then
If Err.Number 1004 Then
For Each objCubeFld In .CubeFields
objOutputArea.Offset(iRow, iColumn) = _
"Cube Field Names - " & objCubeFld.name
iRow = iRow + 1
Next objCubeFld
End If
End If

If .DisplayNullString = True And _
Len(.NullString) 0 Then
objOutputArea.Offset(iRow, iColumn) = _
"Custom Null String: " & .NullString
iRow = iRow + 1
End If

If .DisplayErrorString = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Custom Error String: " & .ErrorString
iRow = iRow + 1
End If

If .EnableDrilldown = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Drilldown is enabled"
iRow = iRow + 1
End If

If .ShowDetail = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Inner Detail: " & .InnerDetail
iRow = iRow + 1
End If

If .ManualUpdate = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Manual Update is ON"
Else
objOutputArea.Offset(iRow, iColumn) = _
"Automatic Update is ON"
End If
iRow = iRow + 1

If .MergeLabels = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Merge Labels is ON"
iRow = iRow + 1
End If

objOutputArea.Offset(iRow, iColumn) = _
"Pivot Table Refresh Rate: " & _
.PivotCache.RefreshPeriod
iRow = iRow + 1

objOutputArea.Offset(iRow, iColumn) = _
"Last Refresh Date: " & .RefreshDate
iRow = iRow + 1

objOutputArea.Offset(iRow, iColumn) = _
"Data last refreshed by: " & .RefreshName
iRow = iRow + 1

If .SaveData = True Then
objOutputArea.Offset(iRow, iColumn) = _
"Data for Pivot Table report is " & _
"saved with the workbook"
Else
objOutputArea.Offset(iRow, iColumn) = _
"Data for Pivot Table report is " & _
"NOT saved with the workbook"
End If
iRow = iRow + 2

objOutputArea.Offset(iRow, _
iColumn).Interior.ColorIndex = 42

End With
iRow = 2
If blnMakeComment = True Then
Call MakeComment(strComment, _
.PivotTables(z).TableRange2.Address)
End If
Next z
End With
End If
blnMakeComment = False
Next x

Set objOutputArea = Nothing

Cells.Select
Selection.ColumnWidth = 2
Cells.EntireColumn.AutoFit
ActiveWindow.Zoom = 75

For d = 1 To _
ActiveSheet.Cells.SpecialCells(xlLastCell).Column
If Columns(d).ColumnWidth > 125 Then
With Columns(d)
.ColumnWidth = 125
.WrapText = True
End With
End If
Next d

Range("A1").Select

If iPtCount = 0 Then
'turn warning messages off
Application.DisplayAlerts = False
ActiveSheet.Delete
'turn warning messages on
Application.DisplayAlerts = True
MsgBox _
"There are no Pivot Tables in the active workbook..." & _
vbCr & _
vbCr & Chr(34) & ActiveWorkbook.FullName & Chr(34), _
vbCritical + vbOKOnly, "Warning..."
Else
'format for printing
With ActiveSheet.PageSetup
.PrintGridlines = True
.PrintTitleRows = "$1:$6"
.Orientation = xlPortrait
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = iPtCount
.FitToPagesTall = False
.CenterHorizontally = True
.CenterVertically = False
End With
End If

're-hide previously hidden sheets
On Error Resume Next

y = UBound(aryHiddensheets)
For Each objSheet In ActiveWorkbook.Sheets
For x = 1 To y
If objSheet.name = aryHiddensheets(x) Then
objSheet.Visible = False
End If
Next x
Next objSheet

If iPtCount 0 Then
Application.Dialogs(xlDialogWorkbookName).Show
End If

End Sub
'/=====================================/
Private Sub MakeComment(strDetailInfo As String, _
strAddress As String)
'create comment with pivot information in it [strDetailInfo]
'strAddress is full address of Pivot Table being processed
Dim strFirstCellInAddress As String

'get first cell in range
strFirstCellInAddress = GetFirstCell(strAddress)

'if a comment exists, delete it if created by an earlier run
' of this macro, then create a new one
If CommentExists(strFirstCellInAddress) = False Then
Range(strFirstCellInAddress).AddComment
Else
If UCase(Left( _
Range(strFirstCellInAddress).Comment.Text, 16)) = _
"PIVOT TABLE NAME" Then
Range(strFirstCellInAddress).Comment.Delete
Range(strFirstCellInAddress).AddComment
End If
End If

With Range(strFirstCellInAddress).Comment
.Visible = False

If Len(.Text) > 0 Then
.Text Text:=.Text & Chr(10) & strDetailInfo
Else
.Text Text:=strDetailInfo
End If

.Shape.ScaleHeight 1.75, msoFalse, msoScaleFromTopLeft
.Shape.ScaleWidth 2, msoFalse, msoScaleFromTopLeft

' .Visible = True
End With

End Sub
'/=============================================/
Private Function CommentExists(strRng As String) As Boolean
'test if there is a comment in the current range [strRng]
'return False if no Comment / True if cell has comment
Dim rng As Range
On Error GoTo err_Function

CommentExists = True

Set rng = Range(strRng)

If rng.Comment Is Nothing Then
CommentExists = False
End If

' Set cmtComment = rng.Comment
' If cmtComment Is Nothing Then
' CommentExists = False
' End If

exit_Function:
Set rng = Nothing
Exit Function

err_Function:
CommentExists = False
GoTo exit_Function

End Function
'/=============================================/
Private Function GetFirstCell(strFullRng As String) As String
'get 1st cell in a range / Return offset of 2 columns
'for example: in $A$5:$D$9, $C$5 is returned
Dim rng As Range
Dim strFirstCell As String
On Error GoTo err_Function

strFirstCell = _
Left(strFullRng, _
Application.WorksheetFunction.Find(":", strFullRng) - 1)

Set rng = Range(strFirstCell).Offset(0, 2)

GetFirstCell = rng.Address

exit_Function:
Set rng = Nothing
Exit Function

err_Function:
GetFirstCell = "C1"
GoTo exit_Function

End Function
'/=============================================/

--
HTH,
Gary Brown
Hi,
>
> I want to see the souce field for each of page field,row field,column field
> and data field of pivot table. if I right click on the pivot table and
> select the field settings property , I could get the "source field" on the
> top only for data fields . I couldn't see the source for page fields. Is
> there any way to see the source for page fields?
> Thanks,
> Krishna

Hi,

I'm trying to work out a formula to automatically update the date and time a cell was last updated.

So for example I want cell G1 to display the date and time I last updated cell H1.

Is this possible?

Hi,

i have a report of which i have attached a sample view of it

what i want is to be able to highlight/indicate which is the latest update according to the date/time stamp

this report shows the changes to a specific text field in a program and each change is reflected as a separate field with old value, new value, date/time of change etc (See attachment).

therefore one specific refererence text might have 4 changes to it(some might have up to 1000 changes), what i want to do is somehow indicate easily which row shows the latest change to save time having to filter through the changes and the date/time. If i can filter the Reference Field column then have it mark/indicate which is the latest change that would be great.

i have created the countif column but i am not sure if this helps me in the long run and Vlookups.

Each reference field is located in this one worksheet and they cannot be broken up into separate sheets. the attachment sample shows 2 different reference fields with 4 changes each.
IF the output comes in a different sheet then that is fine, but the main function of this report sample cannot change too much

thanks

First of all, I don't really know VBA more than just piecing together other people's work (so please dumb down your responses as much as possible).

So I would like to have a function that displays a filename within a cell. The filename I need to display will start with "cost" and have a date at the end (not always the same number of characters). I need to search a folder for all files that contain that text and return the last modified one.

I found this VBA code that returns the last modified file, but only filters based on the extension (which I would like to keep in the code as well).

Function GetRecentFile(DirPath As String, Extension As Variant, _
    Optional LeastRecent As Boolean = False) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetRecentFile
' By Chip Pearson, www.cpearson.com/Excel, chip@cpearson.com
'
' This procedure returns the most recent or least recent file name in a folder
' specified by DirPath having an extension specified by Extension.
' The parameters are as follows:
'
'       DirPath         The folder to search. This folder must be a fully
'                       qualified (drive and folder info) folder name and this
'                       folder must exist.
'
'       Extension       The file extension to match. This may be a simple string,
'                       (e.g., "xls" for Excel 97/2003 workbooks), an array of
'                       strings (e.g., Array("xls","xlsm","xlsx") for Excel
'                       97/2003 and 2007 workbooks). If Extension is either
'                       vbNullString or "*", all file extensions are included.
'
'       LeastRecent     If omitted or FALSE, the most recently modified file
'                       is returned. If TRUE, the least recently modified file
'                       is returned.
'
' The result is the fully qualifed file name of the most or least recent file
' name or vbNullString if no matching files were found in DirPath. vbNullString
' is returned if DirPath does not exist or is not accessible.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim SaveDir As String           ' saved CurDir setting
    Dim FileName As String          ' FileName returned by Dir()
    Dim CompareDateTime As Double   ' Last date time of file
    Dim SaveFileName As String      ' Saved FileName
    Dim CurrFileDate As Double      ' Current file datetime returned by Dir()
    Dim Ext As String               ' Temporary file extension to test
    Dim CurrFileExt As String       ' Current file's extension 
    Dim N As Long                   ' Array index variable 
    Dim Pos As Long                 ' Position indicate of extension 
    Dim CompResult As Boolean       ' File test flag 
        
    '''''''''''''''''''''''''''''''''''''''''''''
    ' Save the current working directory setting
    ' and then change the working directory to
    ' DirPath. Exit with result vbNullString if
    ' DirPath does not exist.
    ''''''''''''''''''''''''''''''''''''''''''''' 
    SaveDir = CurDir
    On Error Resume Next
    ChDrive DirPath
    If Err.Number <> 0 Then
        ' Debug.Print "Invalid Path: " & DirPath 
        Exit Function
    End If
    ChDir DirPath
    If Err.Number <> 0 Then
        ' Debug.Print "Invalid Path: " & DirPath 
        Exit Function
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Determine which file types to look at.
    ' If Extension is an array, look at all files.
    ' If Extension is vbNullString or "*", look
    ' at all files. If Extension is not an array
    ' and not "*" and not vbNullString, look
    ' only at the files with the specified
    ' extension. Call Dir() to prime the loop.
    ''''''''''''''''''''''''''''''''''''''''''''''' 
    If IsArray(Extension) = True Then
        FileName = Dir(DirPath & "*.*")
    Else
        If (StrComp(Extension, vbNullString, vbBinaryCompare) = 0) Or _
            (StrComp(Extension, "*", vbBinaryCompare) = 0) Then
            FileName = Dir(DirPath & "*.*")
        Else
            FileName = Dir(DirPath & "*." & Extension)
        End If
    End If
                        
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' If we are looking for the oldest file, we
    ' need to initialize CompareDateTime to a
    ' date past (greater than) any existing file
    ' date. Just use year = 9999.
    '''''''''''''''''''''''''''''''''''''''''''''' 
    If LeastRecent = True Then
        CompareDateTime = DateSerial(9999, 1, 1)
    End If
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' Look at all file names returned by Dir,
    ' looping until Dir returns a vbNullString.
    '''''''''''''''''''''''''''''''''''''''''''''' 
    Do Until FileName = vbNullString
        FileName = DirPath & "" & FileName
        CurrFileDate = FileDateTime(FileName)
        CompResult = False
        '''''''''''''''''''''''''''''''''''''''''''''
        ' Test the current file's modification date
        ' against the stored CompareDateTime. If
        ' the datetime is less than the saved time
        ' (or greater than, in case LeastRecent is
        ' False), set the test flag to True.
        ' Otherwise, don't test the file.
        ''''''''''''''''''''''''''''''''''''''''''''' 
        If LeastRecent = True Then
            If CurrFileDate < CompareDateTime Then
                CompResult = True
            Else
                CompResult = False
            End If
        Else
            If CurrFileDate > CompareDateTime Then
                CompResult = True
            Else
                CompResult = False
            End If
        End If        
        
        If CompResult = True Then
            ''''''''''''''''''''''''''''''''''''''''''''''''''
            ' Get the extension of the current file
            ' and test it against either all the
            ' extensions in the Extension array or
            ' against the specified (single) extension
            ' or, if Extension is either "*" or vbNullString,
            ' against any extension.
            '''''''''''''''''''''''''''''''''''''''''''''''''' 
            Pos = InStrRev(FileName, ".")
            If Pos > 0 Then
                CurrFileExt = Mid(FileName, Pos + 1)            
                If IsArray(Extension) = True Then
                    ''''''''''''''''''''''''''''''''''''''
                    ' Extension is an array. Loop through
                    ' the extensions in the array. If two
                    ' filename differ only by extension and
                    ' have the same date/times, the last
                    ' one returned by Dir() will be the
                    ' result (very unlikely to occur).
                    '''''''''''''''''''''''''''''''''''''' 
                    For N = LBound(Extension) To UBound(Extension)
                        Ext = Extension(N)
                        If StrComp(Ext, CurrFileExt, vbTextCompare) = 0 Then
                            CompareDateTime = CurrFileDate
                            SaveFileName = FileName
                        End If
                    Next N
                Else
                    ''''''''''''''''''''''''''''''''''''''''
                    ' If extension is a "*" or vbNullString,
                    ' then the current file becomes the
                    ' saved file (no testing of file extension
                    ' is done).
                    '''''''''''''''''''''''''''''''''''''''' 
                    If (StrComp(Extension, "*", vbBinaryCompare) = 0) Or _
                        (StrComp(Extension, vbNullString, vbBinaryCompare) = 0) Then
                             CompareDateTime = CurrFileDate
                            SaveFileName = FileName
                    Else
                        '''''''''''''''''''''''''''''''''''''
                        ' Extension was specified. Ensure the
                        ' current FileName has the specified
                        ' extension.
                        ''''''''''''''''''''''''''''''''''''' 
                        If StrComp(CurrFileExt, Extension, vbTextCompare) = 0 Then
                             CompareDateTime = CurrFileDate
                            SaveFileName = FileName
                        End If
                    End If
                End If
        
            End If
        End If
        
        '''''''''''''''''''''''''''''''
        ' Get the next file name from
        ' the Dir function.
        ''''''''''''''''''''''''''''''' 
        FileName = Dir()
    Loop
    
    '''''''''''''''''''''''''''''''''''
    ' Restore the current working
    ' directory and return SaveFileName
    ' as the result. If no matching file
    ' was found, SaveFileName will be
    ' vbNullString.
    ''''''''''''''''''''''''''''''''''' 
    ChDrive SaveDir
    ChDir SaveDir
    GetRecentFile = SaveFileName
End Function
I found a code that would filter the files but don't know how or where exactly to put it in:
Any help would be greatly appreciated.

EDIT: By the way, I would like to have the part of the filename used to filter be taken from a cell. In other words, if the beginning of the filename I am searching for changes, I would like for users to be able to update the search criteria in a cell rather than having to open the VBA code. This isn't necessary but would be a plus.

I'm trying to use the code below to set up my footer, and it's working just fine, except for the formatting of the time.
Sub UpdateFooter()
   ActiveSheet.PageSetup.RightFooter = "Last Updated: " & Format(Date, "dddd,mmmm,dd,yyyy") &
" @ " & Time
End Sub
It currently displays like this:
I'd like it to display like this:
	

	
How do I modify my TIME code to do that?


No luck finding an answer? You could always try Google.