Getting Max Date From List

Jun 3, 2012

I'm using the following code (borrowed from another site) to output the DateLastModified of 4 log files to cells A1:A4:

Function FileLastModified(strFullFileName As String)
Dim fs As Object, f As Object, s As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(strFullFileName)
s = vbCrLf & f.DateLastModified

[Code] .....

The issue I'm having is that the output from the above seems to contain a large number of proceeding spaces. These can be removed with the Clean Function, leaving me with 4 resulting cells A5:A8 which look like this, and are correct:

03/06/2012 14:32:59
02/06/2012 23:27:31
01/06/2012 01:23:20
03/06/2012 12:46:17

What I want to do is use the MAX function to return the latest date and time from the list of 4 into cell A10. This doesn't seem to be working, from what I have read looking at other sites this is because the data may be in the wrong format, though I'm not sure how as the cells are formatted to the correct custom format dd/mm/yy hh/mm/ss, changing the format of this cell hasn't worked either.

View 5 Replies


ADVERTISEMENT

Extract Latest Date To Particular Date From Different Date List

Mar 9, 2014

i have several styles to handle mentioned under different rows separately.

all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y

now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC

View 1 Replies View Related

Excel 2010 :: How To Reference Unit Price In List Two Based On Date In List One

Mar 8, 2012

I'm using Excel 2010 on a Windows 7 machine.

I have one sheet with a large list of dates and total prices.

I have a second sheet with a list of dates and unit prices.

I want to divide the total price in my first list by the unit price on my second list.

I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.

My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.

View 2 Replies View Related

How To Find Last Date In A List

Jan 20, 2014

I'm trying to find a formula that can be input in the yellow cells shown, which automatically searches for the last date seen/entered for that particular client. I wish to be able to sort Column A and look for all client's seen in say "January" and it show the last date the client was seen that year. Hopefully the example will be clear.

match dates.xlsx‎

View 2 Replies View Related

Non Countin #N/A In Date List

May 1, 2009

I have a list of dates that runs a chart, the list has certain #N/A which keeps the chart not showing 0's, however i need to count the number of dates in the given list.

View 2 Replies View Related

Max Date Only From A List Of Part

Jan 19, 2010

I have a list of about 200 parts that shipped. However, each part shipped several times over the last year. I probably have 3,000 - 4,000 lines total.

What is need is each part once, with the last shipped date.

View 9 Replies View Related

Finding Closest Date In A List

Apr 9, 2008

I've been turning this problem over it my head for the last day and can't seem to come up with a decent solution, so I figured I'd post it here to see if anybody else had any bright ideas.

I have a data sheet with a dropdown list on it to select the month and year that determines how the rest of the data sheet populates. Basically I'm giving weekly breakdowns of data for a month at a time, with the "Week Ending" heading (which are dates - Saturdays), populating off of the month and year selected from the dropdown.

So, to give an example, if somebody selects March 2008 in the dropdown, it will populate the week ending dates of 3/1, 3/8, 3/15, 3/22, 3/29, and then pull out the specific data for each week based on those dates.

What I'd like to do is write a formula that will find the first Saturday of the month. The way I have it jury-rigged right now is a list of months with the first Saturday listed next to it and a vlookup to grab the date of the first Saturday. I envision using this report indefinitely into the future and I'd like to avoid having to keep adding/changing the manual list of Saturdays, instead preferring to have a way to determine the date.

View 9 Replies View Related

Automatic Date: From A Validated List

Mar 10, 2009

In Column G I have a Drop down list of dates. In Column H I want to show the month for the dates, as per when they are selected. I tried simple doing in cell H2 "=G2" they changing the format of the cell for just the month. which worked but for the cells in H that haven't had a dates selected yet it brings back January-00 all the way down. I need a way of getting rid of the January-00 but having the cells ready so that wen the date in G is selected the month auto appears in H.

View 2 Replies View Related

Macro To Change Date List

Feb 19, 2009

i have a list of dates from A1 to A31 , say in january 2009. from the 1st to the 31st, Im trying to get a macro that when i run it it removes all these dates and replaces them with feburarys dates 1st to the 28th. run the macro again and it changes the dates to march etc etc.

View 4 Replies View Related

List Date Adjacent To Duplicates?

Feb 23, 2006

I have a list of Dates in Col. A
Column B contains both numerical and text values.

I need to define a value in column B, and create a list of the dates
that these occured on, on another sheet. Auto filter doesn't work
because there are several different columns. If I try to use it I also
get the values in the other columns.

A B
1/2 8
1/3 4
1/4 Vac
1/5 8
1/6 7
1/7 Vac
1/8 8

Value needed = Vac

Solution 1/4
1/7

View 12 Replies View Related

Calculating 10th Date From List

Sep 20, 2008

I'm using the following formula to find the 1st date that a particular rep wrote an order.

{=IF(S4="","",IF(MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))=0,"",MINA(IF('Daily Compliance'!$A$2:$A$5028=B4,'Daily Compliance'!$O$2:$O$5028))))}

I also need to figure out when they write their 10th order, but have had no success figuring that out.

View 6 Replies View Related

Total Count From List By Date

Dec 5, 2011

I am looking for a formula to return a total of items used within a calender month

I have a list of parts used as below

Column A _ Part Number
Column B _ Part Description
Column C _ Price
Column D _ Date

The list will continually be added to, on a daily basis so will grow and grow in size

each row has the relevant part number etc

I am looking for

Column G to have January 2011 total
Column H to have February 2011 total
Column I to have March 2011 total

etc etc.......

View 7 Replies View Related

How To Make List Of Members By Date

Apr 11, 2014

I have a worksheet with Names in Column A. Date when the person joined in Column B.

On a separate worksheet I want to sort members by the months and year they joined by having all the months in Columns.

View 9 Replies View Related

In A Sub I Need To Check If A Date In List Of Dates

Feb 3, 2007

dim MyRange as range
dim Col as integer

Set MyRange = ActiveSheet.Range(2,1), Cells(102, 1))

if application.worksheetfunction.Match(ActiveSheet.Cells(5, col),MyRange,0) > 1 then
' it was in the list.....

else
'it is not....
end if

I imagine this isn't the best way and I since I couldn't get past the Set statement. What am I missing? Is the range of dates causing the problem? If my range is one cell in the range it sets the range?

View 9 Replies View Related

2nd , 3rd And 4th Date Find From List Of Dates

Aug 22, 2008

how to find the 2nd, 3rd and 4th date from list of dates.....

View 9 Replies View Related

Generate A List With The Date Of Every Other Friday

May 30, 2009

On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:

Private Sub UserForm_Initialize()
' Populate the ListBox control.
listPayPeriodEndDates.AddItem "04/03/09"
listPayPeriodEndDates.AddItem "04/17/09"
listPayPeriodEndDates.AddItem "05/01/09"
listPayPeriodEndDates.AddItem "05/15/09"
listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
listPayPeriodEndDates.AddItem "06/19/09"
listPayPeriodEndDates.AddItem "07/03/09"
listPayPeriodEndDates.AddItem "07/17/09"
listPayPeriodEndDates.AddItem "07/31/09"
listPayPeriodEndDates.AddItem "08/07/09"
listPayPeriodEndDates.AddItem "08/21/09"
listPayPeriodEndDates.AddItem "09/04/09"........................

What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).

View 10 Replies View Related

List All Files In Directory Modified After Certain Date

Sep 5, 2012

I wish to create a macro that looks in a specific directory "D:Records" . It finds the files which are modified after a date "January 01, 2012" in this directory (pdf files). Then it imports these files names into a worksheet.

View 8 Replies View Related

Sum Product To Count Data In A Date List

Feb 23, 2010

I have been using the sumproduct formula to count how many times a certain piece of data appears...which works fine...for text purposes such as names, initials etc...

I now need to amend the formula to count how many times a date appears in a range of dates.

I know when you use the sumproduct...dates must be input and formatted as text.

example

A1
01-Jan
02-Jan
5-Feb
7-Feb
12-Feb

formula to only count dates with feb in?

View 12 Replies View Related

Generate File List With Name / Date / Size

Jun 10, 2013

I have a folder containing about 100,000 files and subfolders. Approximately 20% of the files are duplicates. I would like to create a list of all the files with some details so that I can sort through and identify which ones are duplicates, then create a batch file to delete them. How to get a file list with additional details, like size, modified date, etc, into an excel format?

View 3 Replies View Related

Auto Date Stamp With Drop Down List?

Feb 18, 2009

I'm producing a new spreadsheet to track issues and thier completion. I've used the code I have on another spreadsheet, that I got off this site, thanks. What I would like to change are the following:

1) The status column (F) uses a drop down list, located in cells Z1 to Z5.
When an option is selected, the date updates in column G automatically.
What I would like is for the date to only update in column G if the drop
down list is changed to Completed.

2) If the status is changed to completed by mistake, and then changed back
I would like the date to disappear.

Finally, can I make the date columns jump automatically so the person entering the data goes from column F to column H.

View 3 Replies View Related

List Most Recent File Creation Date

Nov 20, 2009

I put together some code which at the end of it I am wanting to show in cell D1 the creation date for the most recently created file found.

View 5 Replies View Related

Create List Of All Sales Older Than Specified Date

Jan 26, 2012

Code that will do the following,

In sheet2 cell B2 i have a date (for example 12 Jan 2011)

I want to look at sheet1 columB and if the date is older than the date in sheet2 cell B2, then copy then all the data from that line in sheet1 be copied over to the first free line in sheet2 I need the data from colums B to ZZ to be copied.

View 1 Replies View Related

Populate List Of Months Based On Date & #

Jul 15, 2009

I have a # of months in P11, and a Start Date in P12:

P119 Months126/1/2024

I need to excel to autopopulate monthly dates from the Start Date = to the # of months displayed in P11: ...

View 17 Replies View Related

List Files In Directory With Date Modified

Nov 28, 2006

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 3 Replies View Related

Drop-Down List With Dates Before & After Current Date

Dec 10, 2006

Is there a way to create a drop down list using Data/Validation that will create a list of dates that look ten days back and that look ten days ahead?

View 4 Replies View Related

List Values Meeting Date Criteria

Sep 30, 2007

I cannot get my head around this loop, it has been a while since I did anything in VBA and am stuck. I have a list of trades on one sheet, would like to create a column that lists open trades as of that calendar day. Using Sheet(balance) column A as date range. What I am trying to do is test for condition if opening day is less than calendar day AND closing day is more than calendar day...then that trade is effectively open as of that calendar day...so would like to copy it with that calendar day in front of it.

Loop through sheet x and copy all that meet above criteria. Next calendar day, loop through all trades on sheet(x) again and copy etc...here is what I have so far, but I am afraid it is useless...

View 9 Replies View Related

Extract Names From List Using Multiple Date Criteria

May 16, 2014

Say, I have 100 names , in that two names /employees joined during the week of 04/21/2014-04/25/2014 - 5 business days and one resigned during the week - Thats my first worksheet Which gives the employee name , Employee id , Joining Date , Resignation Date.

Second worksheet i am giving a summary -

In that i would like to get only the names joined during the week and resigned during the week

Is it possible ? Will the offset function can provide me the desired result ?

View 4 Replies View Related

List Of Weeks From Start Date And Input Number

Apr 16, 2014

I am trying to create a simple set up where the operator can enter a starting date and the number of weeks they want this event to occur. This then produces a column of dates from the starting date to the end date for the number of week entered.

E.g. - start date = 16/04/2014 - No. of Weeks = 3

The output would be as below

16/04/2014
23/04/2014
30/04/2014

View 4 Replies View Related

Removing Duplicates From List Based On Latest Date?

Jan 6, 2014

I am working on a large data file (leasing file), that has many duplicates. The names on the file are duplicated due to the various variable costs associated with leasing. I need to remove the duplicates names based on the latest contract end date.

View 4 Replies View Related

Create A List Of Appointments That Match A Time And Date?

Mar 17, 2014

I have a workbook that requires the user to enter data for an appointment calendar.I need a formula that will list all the appointmets that are entered for a seleted date and time.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved