Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Last Modified Date


I have a number of excel files in a directory that require me to take action after a file has been saved. What I want to do is create a macro that will look to each file and retrieve its 'modified date', then put that into a spreadsheet. What would be ideal is to create a User Defined Function to look at the last modified date of a file in the cell next to it (I have the files names in a spreadsheet as a hyperlink).


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
MSG Box - Date Modified
I am sure this is easy but i cant for the life of me find the code for this:

I am trying to have a MSG Box popup after opening a file and it displays the date the current file was last modified with an "OK" button on it to close the box.

View Replies!   View Related
Date Last Modified (in Cell)
I want to know if there is a way to have a cell beside a series of cells that will change its date (and time) when any of the cells in the series is changed.

On a single row, I have four materials that total to a fifth cell. The 6th cell in that row, I would like to have a "date last modified" that would change if any of the first five cell's value is changed.

View Replies!   View Related
Determine Last Modified Date
I have a pivot table for which I load data from a .csv file. Is it possible to include a timestamp on when the .csv file was last loaded. If I do it above the actual pivot table, it just shows when the pivot table was refreshed, but not when the .csv file was last loaded.

View Replies!   View Related
Date Modified Of Text .txt File
I need to be able to find out which of two .txt files were most recently modified, the two txt files are of fixed name so I can hard code these.

View Replies!   View Related
Date Stamp When Cells In Row Are Modified
I am trying to find a way for an automatic date stamp to be populated into a field in a row when any other cell contained within the same row is changed or modified. I am hoping that there is an easy solution to this. I have tried searching for this and found a few related posts but I don't know how to modify the code to work for me and when I try to do it, it doesn't work. I have attached an example. I would like column B to populate when I modify any field in the row with the date it is being modified.

View Replies!   View Related
Get The Modified And Open Date Of An External Workbook
I have a list that show file names and when the necessary works on these files were completed, but the powers that be want more. I have been asked to add two more columns, one showing when the file was last opened and the other when it was last modified.

Is it possible to do this through VBA without having to open the files that are listed? If so I will run the macro when the workbook is first opened.

View Replies!   View Related
Static Date Insert - Change When Modified
I have a spreadsheet that has the date in one cell. I want the date to remain static unless I make a change. ie, when I first create the sheet I want a static date to go into this cell. Then I only want this date to change if any changes are made to the workbook (several sheets), I dont want the date to change everytime I open the spreadsheet. It must however update if I make any changes to particular cells on various sheets.

View Replies!   View Related
Application.FileSearch To Obtain Last Modified Date
I've been trying to get this to function correctly. The following code does pull out a list of all filepaths, but what I want to to is access the properties of the file.

Basically I select a customer from a combo box, and from this the code does a vlookup to determine what folder the reports are stored in. The list is then entered onto the worksheet 'FilesInReportFolder'. There is some commented out code as I've ben trying to come up with a simple way to remove the 'xls' from the end of the string, but din't worry about that.

I've found a website that suggests adding .name to the lineto get the name instead of the path, but what I really want is be able to pull out other details about the file.

Here's my

Private Sub ComboBox1_Change()

Combobox1value = ComboBox1.Value

Dim rng As Range
Dim RowNo As Integer
Dim NoOfFiles As Integer
Dim foundfilepath As Integer
Dim FilePath As String

View Replies!   View Related
Open Latest File By Date Modified
I have manged to search for the last updated file on a folder but i want to use the filename as the source for a copy and paste. it all seems to work bar the linking of the 2 processes the error message 'run time error 9 subscript out of range' is the file is already open should it just not use the same variable to copy the files?


Option Explicit

Sub Get_Availability_Data()

Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Dim wsTo As Worksheet, wsFrom As Worksheet

Dim myDir As String, fn As String, a(), n As Long, myFile As String
Dim myDate As Date, temp As Date
myDir = "C:BarkingEMCIN"
fn = Dir(myDir & "*.csv")
Do While fn <> ""
temp = CreateObject("Scripting.FileSystemObject").GetFile(myDir & "" & ................................................

View Replies!   View Related
Showing File Last Modified Date In A Cell?
if there's any sort of script which allows the file's last modified date to be shown in a worksheet?

View Replies!   View Related
List Files In Directory With Date Modified
I currently have a macro working where I can populate the A Column with files from a directory and based on its file extension, however I am having problems trying to populate the second (B) column with that files date modified. I have searched the forum to no avail and I must admit I not the best with vba.

Sub INP_files()
' searches within the file location
' C:Arenium_Projects31-1853012_Barnawartha BOSCADDMX
Dim rngOut As Range
Dim strPath As String
Dim strfile As String
' Sheets("INI_FILES").Select

View Replies!   View Related
VBA - Generate Date Range Within 2 Supplied Dates (Modified)
I just wanted to understand something about some code that was supplied to me on this forum. The code works perfectly, with the exception of one minor issue. The original thread and solution presented is found here

View Replies!   View Related
Modify/Edit File Properties (Date Modified) Of Workbook
I want to amend the ' Date Modified' for a spreadsheet. I have just made changes to it - so the 'Date Modified' shows today's date and time. Is there a way of changing the 'Date Modified' so it shows as yesterday's date?

View Replies!   View Related
Search Folders & Subfolders For Files By Date Modified & Move/Copy Them
I'm trying to make a macro that will search a folder and move all files from 2008 into another folder. So far it can do this, but what i need is for it to search subfolders also. At this point it moves all files from the main folder, but nothing from the subfolders.

Option Explicit
' Reference site [url]

Sub Copy_and_Rename_To_New_Folder()

Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim PathExists As Boolean
Dim objFile As File
Dim SrcFolder As String
Dim DestFolder As String
Dim x, Counter As Integer, Overwrite As String, strNewFileName As String
Dim strName As String, strMid As String, strExt As String
Dim LastModYear

View Replies!   View Related
Can This Code Be Modified To Run Faster
way to bring more efficiency in this code.

I need to run this code on 100,000 rows every week and this takes hours to finish.

View Replies!   View Related
Modified A Save As Vba Code
I'm trying to modified this line code. The 1st line code below opens a dialog box where you wan to save it.

strPictureFile = Application.GetSaveAsFilename("", "JPEG Files (*.jpeg), *.jpeg", , "Save as JPEG")

Now here I tried to modified it to just save it in a file folder as a jpeg. But its giving me a error. Am I missing something?

strPictureFile = Application.SaveAsFilename = "C:MyPictureD.jpeg"

View Replies!   View Related
VBA Modified To Capitalize First Letter
i found this VBA but i need it to change it for more columns not just column 3 ("C") i also need D F G J T W. how can this formula be modified?

View Replies!   View Related
Function To Return Last Modified File
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).

View Replies!   View Related
Sort Each Time The Stats Are Modified
I have used the "sort" function on many occasion, and I am now stumped as to why it wont work

I have a table with 9 columns: Team, Games played, Games won, Games tied, games lost, goas for, goals against, diff and points.

Each of the collums and rows have different calculators in them.

I want to sort each time the stats are modified, to show who is is first place, second etc. Thus the sort is by Points first, then Diff.


View Replies!   View Related
Modified Compare And Delete Across Sheets
This compares cells in every sheet to a master sheet "AllUnits" and deletes all the matched rows on the Master. I modified it slightly to also delete the matched row on the source sheet as well (looking for rows that are not matched on any sheet). This works to a point, but I have to re-run it many times since when the source row is deleted, the code actually skips to the next row. I tried to reset the source variable with

View Replies!   View Related
Prevent Some Drawing Objects Being Modified
How do i preventing drawing objects from being modified/ deleted/added on an excel worksheet? edit: but i want people to be able to interact with them i.e. people shud be able to enter text into textboxes.

View Replies!   View Related
Modified Transpose Function OR Macro?
I am trying to perform the following, but am unsure if a macro is needed, or if there is a way to do it by possibly modifying the transpose function. Here are the steps required:

1 - copy a selected range of cells from a single row in one worksheet (i.e. D3:AE3).

2 - paste the data from those cells into a selected range of cells from a single column (i.e. A1:A14) in a separate worksheet BUT skip the cells from D3:AE3 that were blank instead of having them create blank cells in the column of the target worksheet

3 - Need to maintain a reference back to the original row of cells so that if their values change (i.e. D3 from source), the corresponding values in the new column (i.e. A1 in target) are auto updated.

View Replies!   View Related
Checking File Created And Modified Dates
Is there a way I can check an excel workbook's created and last modifed dates using a macro in that same workbook?

View Replies!   View Related
Subscript Out Of Range :: Modified Version Of J Walks Input
I am getting Subscript out of range when I call a modified version of J Walks input form using excel 2007. It works in one workbook but not this one.

Option Explicit
Public Const LANGUAGE As Long = 1
Public Const APPNAME As String = "Data Entry Form" 'displayed in title bars
Type CriteriaData
FieldNumber As Long .....

View Replies!   View Related
Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula
My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.

Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 2008-07-03) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.

This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.

Here is a copy of my formulas:

=IF(AND(F8)=0,""*(IF(AND(F8)

View Replies!   View Related
Compute The FuTure Date When Start Date,Lead Date,Weekly Offs,Leaves And Holidays
First and foremost I would like to congragulate you on this wondeful piece of code in the below link...

The query was to get a future date excluding Fridays and Holidays...

http://www.excelforum.com/excel-work...rkingdays.html

I have a similar query and therefore I pasted this link...

I actually wanted to get a future date using a Dynamic two day off as my the offs keep on changing as well as incorporate Holidays and Leaves if any..

Now Holidays would be official Public Holidays and
Leaves would be taken by the employee..

The code needs to pick the Leaves + Holidays and different offs maybe even more than 2 offs...


View Replies!   View Related
Sort Table In Date Order But With The Date Nearest To Today's Date At The Top
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...

The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...

I use xl 2003.

View Replies!   View Related
Date Range: Reference A Single Date And Output Date Ranges
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:

Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25

and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.

View Replies!   View Related
Fixed Date: Lock The Date To The Initial Date
i would like to use the formula "now()" to establish a date in a document. is it possible to lock this date to the initial date? (so when the document is created the date won't change the next day)

View Replies!   View Related
IF Date Formula: IF Formula, That States If A Date Is More Than A Month After The Date In The Cell Then It Is Timely
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.

View Replies!   View Related
Compare Date: Compare The Date From The User Input And The Date Listed
I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))

Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long
lngBegin = 9 'beggining of data
lngEnd = 232 'end of data
lngCmp = InputBox("Please enter the date", "Begining of the week")

Lngcmp1 = DateAdd("d", 1, lngCmp)
lngCmp2 = DateAdd("d", 2, lngCmp)
lngCmp3 = DateAdd("d", 3, lngCmp)
lngCmp4 = DateAdd("d", 4, lngCmp)
lngCmp5 = DateAdd("d", 5, lngCmp)

'lngCmp1 = lngCmp + 1
'lngCmp2 = lngCmp + 2
'lngCmp3 = lngCmp + 3
'lngCmp4 = lngCmp + 4
'lngCmp5 = lngCmp + 5
lngResults = 0
lngResults1 = 0
lngResults2 = 0..................................

View Replies!   View Related
Calculate Number Of Days Between Two Date Within A Current Month Including End Date
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.

For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.


View Replies!   View Related
Macro To Alert If Today's Date Is Within 5 Days Of Expected Delivery Date
I have a worksheet that has a sent date and expected delivery date I need create a macro that will alert me if today's date is within 5 days of expected delivery date.

View Replies!   View Related
Date Countdown: Results Of The Number Of Days Between Today And A Future Date
I am trying to get the results of the number of days between today and a future date. I am using ="cell containing futuredate"-today() and it gets me the correct number of days. The problem comes in when I have yet to populate the future dates. I am getting -39991 (numeric value between today and jan 01 01) and because I am also using conditional formatting this is even more of a problem. Is there a way get excel to display nothing if it is a negative number? or to give a specified resut if the number becomes negative such as Expired or something of that nature?

View Replies!   View Related
Calculate The Number Of Days From A Date Entered Into Cell A1 To Today's Date
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:

5/10/2009 to today is -9

5/22/2009 to today is 3

View Replies!   View Related
Compare Dates To Date Range & Return Date Based On Outcome
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.

To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet

View Replies!   View Related
Formula For Determining If Two Date Columns Fall Within Specific Date Range
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.

If:

A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)

View Replies!   View Related
Pull Date & Times Into Seperate Cells From Date & Time Cell
DONNAS [Single cell, 1st row] 09/08/2003 15:34 [Single cell ( date and time combined) 2nd row]. Do you know of any way that I can move the name, date and time from the above cells to three separate columns on the same row?

View Replies!   View Related
Count Macro :: Rows That Have An Inclusion Date, But Not An Exclusion Date
I have data in 3 columns: Name, Inclusion Date, Exclusion Date. In some instances, the inclusion date and exclusion date will be zeroed out - I do not wish to count those. What I would like to count is the rows that have an inclusion date, but not an exclusion date. Ideally, I would like to have a code so that when I run a macro a msgbox appears that indicates how many members there are.

View Replies!   View Related
Extract Character From Date Format & Number Of Days Between Date
I have two questions regarding date format and hope you can provide input.

1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?

2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?

View Replies!   View Related
SumIF With Dates: SUM All Expenses After The Posted Date Including That Date
In the expense log, Column C is a list of Dates and Column I is a list of expenses. I want to Sum the expenses in the 'Expense Log 09' to a new sheet based on a Date entered in H24 on the new sheet. I have tried the formula as shown below and Get the result #NAME?

=SUM(IF(Expense Log 'Expenses Log 09'!C8:C100,H24,'Expenses Log 09'!I8:I100)). I would Like to SUM all expenses After the posted date including that date.

View Replies!   View Related
Vbcodes To Select Start Date Then Begins Highlight Until End Date
i need VB to select the start date of booking and autohighlights the "blank booking slot cells" until the end date that is input in Excel. You can visualise this as a room booking system. The left most column is the dates and the first row represents the different rooms type. E.g. if one guest books the Twin Deluxe No. 123 room on 23 Oct 2006 until 25 Oct 2006. We wish the VB can essentially help us identify the start date then begins highlighting the three slots for 23, 24 and 25 Oct on the column for the Twin Deluxe Room 123.

View Replies!   View Related
If Date Cell Less Than Todays Date Calculate Difference In Days
I have column B with a heading "Days Remaining" and column L with a heading "Deadline". starting with row 5, I need to be able to enter a date in L5 and see the days I have left, from that day untill today, on B5. I need to then be able to enter a date into L6 and see a result in B6 and on and on. Then I need to be able to insert or delete a column and have the formulas still work in the columns with the heading "Days Remaining" and "Deadline"

View Replies!   View Related
Prevent Automatic Date Format When Referencing Date Cell
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.

View Replies!   View Related
Copy Date Heading Columns To Sheet Named Same As Date
Did anyone use the above tool ? Can we use a macro to launch that tool to search for words in a excel cell ?

View Replies!   View Related
Check Cell Has Date & Insert Current Static Date
I have a user form that when a command button is clicked it enters the data from the from into coloums a,b,c,etc. I have code to do this but I want to add code to add a static date in coloum B based on if coloum A had data entered from the form. I need the date not to change to current date when the file is reopened. I am trying to elimate a date text box in the form. I have a link to the file http://www.box.net/shared/zdtsjv0qos

View Replies!   View Related
Calculate Date Difference & Use Current Date If Cell Empty
i am trying to create a forumla in a column (D in my expamle below) to count the number of days between two dates. Knowing that col D will change everytime the @now date changes - but thats ok..

COL A COL B COL D
ROW 1 Open Date Closed Date Count of days open
ROW 2 4/1/07 BLANK CELL Formula result here
ROW 3 4/1/07 9/5/07 Formula result here

if col b has a date then subrtract col a from b and display the # result, if col b is blank then subtract col a from location I store the @ now date - say Col ZZ Row 99? so no matter there is a count of number of days in every row in column D

View Replies!   View Related
Convert Coded Date Into Common Format Date
I'm in the military and for maintenance we use the Julian date the format goes as follows "09357" that is simplified as 09 = the year and 357= the three hundred and fifty seventh day of the year. so 09357= 12,23,2009..

so on to my question:

how can i make excel auto convert the julian date to the regular date and vice versa?

View Replies!   View Related
Restrict Date Entry To Be With Date Range/Scope
I'm having a problem with data validation. I set an entire column so that it could only be a date between the first date in the list and the current date. I tested a few cells to make sure that it was working the way I wanted and I noticed that it allows a random number like 3 or 5 to be input after row 50. This would create a date in the 1900s. Why is this happening and how can I stop it.


View Replies!   View Related
Count Date Cells Where Date Is Previous Month
I have a spreadsheet which I use to track when a work request is recieved, when we confirm the request and when we action the request. I have been trying to write some code to count the amount of requests, receipts and actions we have processed in the last month.

My first column shows who the request is from
The second shows date recieved
The third shows date we send receipt
The fourth shows the date actioned.


View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved