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


Advertisements:










Generate A List With The Date Of Every Other Friday


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Calculating Friday Date
I have dates listed in column A indicating the day an action was performed. In column B I need to have the date in column A changed to a Friday. If the date in column A is not a Friday, then the date in column B needs to fall back to the previous Friday. For example, if the date entered in column A is today, Monday August 7, then the date in column B that I would be Friday August 4. Can anyone think of code that determine this and place the value in column B? Currently I use vlookup to go to another sheet where all the possible dates are listed with their respective Fridays, but this slows down the calculation worksheet as the file is quite large.

View Replies!   View Related
Formula Return Following Wednesday Or Friday Date
Need a formula for cell J27 that returns the date of the following Wednesday from a date in cell J2 that's a Wed, Thurs, or Fri
or
return the date of the following Friday for a date in cell J2 that's Sat, Sun, Mon, or Tue.

For example, when 05-29-08, a Thursday, is entered in cell F2, I need cell J27 to return the following Wednesday's date of 6-04-08.
or
For example, when 06-01-08, a Sunday, is entered in cell F2, I need cell J27 to return the following Friday's date of 06-06-08.

View Replies!   View Related
Change Cell Color If Date Equals To Friday
is there anyway I can do a conditional formatting of something like that to color a cell if the date in the cell is a friday?

Dates will appear in a column like this:
16-mar
17-mar
20-mar (this is a friday, it should be red)
21-mar
21-mar

View Replies!   View Related
Using A List In A Pivot Table To Generate A List On Another Sheet
I have a set of accounts (general ledger accounts) that the accounting group posts expenses to. every once in awhile a new account is added. This is captured through a pivot that i have built (sheet A).

on another sheet (sheetB) i want to display the accounts that are shown in the pivot so that i can forecast their future activity.

What i am trying to figure out is how can i make the list on Sheet B change when new accounts are added to the pivot on Sheet A (without simply referencing the pivot table making one cell equal the other)? I am not looking for a data validation pull down, i want a full list of the accounts.

View Replies!   View Related
Drop-Down List: Generate A List Based On The Value Of A Cell
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15
A drop-down list displays the values 1 through 15. Cell A1 changes to 20
Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.

View Replies!   View Related
Auto Generate List
I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).

There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending.
I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.

The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)

View Replies!   View Related
Generate Three Different Results From One List
Find attached an example sheet. I have one list with data... Name, Handle, Result. I want to have three lists from that data

List 1 would have top ten highest results
List 2 would have top ten lowest results

Some results will be the same so I would need each Name listed as they appear in the main list until ten is reached. List 3 would have top ten handle, but would not include anyone in list 1 or 2. This will be a shared workbook

View Replies!   View Related
Generate Sheets For Each Name In List
I have a long list that is geneerated each week and I need to split them based on their category acrross a number of sheets all named by the category. These sheets don't exist at the beginning.

My list would be like:

Col A Col B
UK Dave
UK Bill
UK Ann
US Bob
AUS Sheila
AUS Bruce

I want to run a macro down the list and build sheets for each unique ColA ie UK, US and AUS and then copy the ColB values into the correct sheets so UK has Dave, Bill and Ann in colA of the sheet named UK. anyone have an example of this type of process.

View Replies!   View Related
Generate New File For Each Entry Of A List
I have a database file with a list of components. Each component has manufacturers name, part number, description etc. I need to create an individual file from each of the components in the list. I would like a quick and easy way to copy the information in each Row and generate a new file for each Row containing the existing template I have.

View Replies!   View Related
Generate List That Match Criteria
I have been working on a project with 30 products and 20 different companies.

I am working on a business case, where there are launch dates for various companies with expected sales, contribution margin, NPV etc.

The idea is that I can create specific scenarios (What is Company1 doesn't launch the products, etc), and there is an accompanying each company. Because it can be difficult to see what products are included in the current calculations, I would like to use some kind of function that produces a list that matches my criteria. (In this case, all cells for a company that are larger than 0).

I would like to find a function that finds all products that have a launch date and create a list of them -- without spaces between non-matched results. And that it updates whenever data is changed (new launch year or new product in company's profile)

I have included a generic excel file to show what I am going for.

View Replies!   View Related
Generate Drop Down List From Another Sheet
I am trying to create a list, I know how to do this on the same sheet, I need to be able to have the list informaiton on a data sheet that contains other information that I need to have to populate other parts of the sheet. then I need to add this list data so that I can have drop down options so that users can pick from selected work descriptions

View Replies!   View Related
Generate Random Numbers From A Frequency List
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.


View Replies!   View Related
Extract Entries From A List And Generate An Array
I have a List like this (a large one in practical)
A B 1 cat dog 2 rat cat 3 bat cat 4 cat rat

At the bottom I want to generate an array which gives what are the text entries of this list.(ignore multiple occurrences)

so the answer should be

cat
rat
bat
dog


View Replies!   View Related
Generate New List And Tally Non Blank Cells
I am trying to make a more useful excel extracted report – what I have to start with is muck like the attached sample file. With at the bottom is what I would like to be auto done.

There is a column of names – with most names repeating so the first thing needed is to create a new summary list – can be in the same worksheet or in a new worksheet. The new list with be just a list of each name but only display each name once rather that the multiple of times as source list.

The second requirement is than once have a list of each name that appears then in columns next to each a tally /count of each non blank cell under each respective column heading - now it has to be a count of non blank cells as the content of each cell will chance – now need to understand it is not a count of numerals as such but a count of non blank cells so it is not 1 + 3 + 5 = 9 but should be 1 +3 + 5 = 3 (3 non blanks).

A idea of what I want is at bottom of sample worksheet attached

View Replies!   View Related
VBA To Generate Row For Each Date In Date Range
I've created a userform to track attendance. The code below creates entries based on the activities undertaken on that day; one row for each activity reflecting Name, ID, date, Activity and comments. The intent is that several activities can be undertaking on a day, and the code generates separate rows for each activity....

View Replies!   View Related
Code To Generate 'date' Ref Number
each time a button is clicked I want a new ref number to be produced and displayed on my userform.
I can't however create the code to generate this ref number.

In column A of my 'sweeplog' worksheet is where I want the ref number to be placed. This ref number is simply made up of today's date - in format ddmmyy eg 240408 for today 24th April 2008 then add the number '1' if its a new date or the next number in coulumn A if not.

for example In column A I have these 3 ref numbers
A2 = 2404081 (24th April 2008 plus 1 as this is the first ref used today)
A3 = 2404082 (as above but with 2 added)
A4 = 2404083 (as above but with 3 added)

now tomorrow the next ref number that would be shown in column A (after the user clicks the button) would be as follows.....

A5 = 2505081 (25th April 2008 plus 1 as this is first ref used on 25th)

I am able to get this unique ref number to show in my userform but can't figure out how to produce the code to generate it

View Replies!   View Related
Generate A Random List In A Column That Adds Up To A Fixed Amount
i had a basic spreadsheet for my incomings and i got wiped with my hd exploding!! i know how much the total was i just need a way of creating a list of random amounts within a specified range over a year to give to my accountant.


View Replies!   View Related
VBA - Generate Date Range Within 2 Supplied Dates
First I would like to thank you for taking the time to assist me. My issue is a follows: I want to have 2 dates (beginning and ending) input on one sheet. With these 2 dates entered, I want to generate a date range between them but have it output on a different worksheet. Here is the function that first pulls the date values entered:

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
Reminder On Every Other Friday
Every other Friday, I need to submit my biweekly work hour sheet to manager. For example, as 12/21/2007 is Friday and pay day, I need to submit my sheet. Next time is two weeks later, 1/4/08, and on.
12/21/07 is the first event.

I want to create a conditional format, or workbook events, or worksheet events to remind me on that Friday when I open that book or activate that sheet.

View Replies!   View Related
DATES: Identify Every Other Friday
I have a column that has dates going vertically down from 1/1/2009 - 1/7/2010. I need a formula that will return the date for every other Friday beginning with the second Friday in January 2009.

View Replies!   View Related
Change Non-working Day To Friday
how can i change nonworking date from saturday&sunday to frinday&Saturday

View Replies!   View Related
Return Last Friday From A Given Month
Re: Return last Friday from a given month
This will calculate the last Friday of the month in which the date in A1 falls:

=DATE(YEAR(A1),MONTH(A1)+1,0)+MOD(-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-2,-7)

HTH

TJ

actually this thread is closed. I want to convert this into UDF but not able.

View Replies!   View Related
First Monday, Thursday, And Friday Of Month
In cell A1 I have a date (like 8/1/08)

In A3, B3, and C3 I need to have the first Monday, Thursday, and Friday of the month. Here's the caveat, I need A3 to have the day that comes earliest in the month.

In this case, A3 would contain 8/1/08 since it's the first Friday, B3 would have 8/4/08 as the first Monday, then C3 would have 8/7/08 as the first Thrusday.

If A1 contained 7/1/08 then A3 would have 7/3/07 as the first Thursday with Friday and Monday following.

View Replies!   View Related
Calculate 1st Tuesday Or 3rd Friday Of Month
I am trying to construct a schedule of tasks to be done by certain dates. If the last day of the month is entered (say 11/31/09) in a cell, is it possible to find out the date for the third Thursday of November or the first Wednesday or the last Friday?

View Replies!   View Related
Run A Macro Automaticly On A Closed Work Book Every Friday
Is there a way to run a macro automaticly on a closed work book every Friday ? I've found ways of doing this using on-open event but It made me wonder if this was possible.


View Replies!   View Related
Counting Number Of "Friday"s In A Range
I was wanting to count the number of Fridays that appear in the range of 1 Jan 2010 - 31 March 2010. I will be changing the searchable day and range in the future so I need it automated. I expect the output to be a number, example. 13.

View Replies!   View Related
Max Date Only From A List Of Part
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 Replies!   View Related
Non Countin #N/A In Date List
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 Replies!   View Related
2nd , 3rd And 4th Date Find From List Of Dates
how to find the 2nd, 3rd and 4th date from list of dates.....

View Replies!   View Related
Finding Closest Date In A List
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 Replies!   View Related
Calculating 10th Date From List
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 Replies!   View Related
List Date Adjacent To Duplicates?
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 Replies!   View Related
In A Sub I Need To Check If A Date In List Of Dates
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 Replies!   View Related
Macro To Change Date List
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 Replies!   View Related
Automatic Date: From A Validated List
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 Replies!   View Related
Sum Product To Count Data In A Date List
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 Replies!   View Related
List Values Meeting Date Criteria
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 Replies!   View Related
List Most Recent File Creation Date
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 Replies!   View Related
Auto Date Stamp With Drop Down List?
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 Replies!   View Related
Populate List Of Months Based On Date & #
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 Replies!   View Related
Drop-Down List With Dates Before & After Current Date
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 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
I Have A List Containing Customer Data.. Can I Insert A Date Automatically?
I have a spreadsheet which I am trying to use to capture customer data. What I want to happen is whenever I enter a new record, the date appears automatically in a Date column ....

View Replies!   View Related
Counting The Number Of Times A Date Appears In A List
the number of days when there were 0 cases
the number of days there was 1 case
the number of days when there were 2 cases.
As yet there are no days in which there were more than 2 cases but there might be in the future.

I have a list of dates when operations took place in that room. On some days the it was not in use, so those dates don't appear in the list. Some days there was 1 case, so that date appears once in the list. On some days there were two cases, so that date appears twice in the list.

What I've done so far is create a pivot table that contains all the dates, then grouped it by day and counted the number of times there was 1 or 2 cases in a day by hand, then subtracted the total to get the days when there weren't any cases.

View Replies!   View Related
Load Week Ending Date From Numbers List
I have a list of entrys (41 in total so far) from cell A1 down starting with the value 1. This entry represents the week ending 04/06/06. ie each entry represents a date for the week ending. So cell A2 will contain the no 2 and represent the week ending 11/06/06. Cell A3 will contain the no 3 and represent the week ending 18/06/06. It goes in order right down to number 41. I have a userform, what I want to do is have a combobox load all the entrys in as dates, not as the entry no. Then when I choose a week in the combobox it will load the appropriate entry no into another textbox? I dont want to add the dates to the spreadsheet.

View Replies!   View Related
Create Sheets With Month Names By Date List
I'm trying to achieve is to write a macro that can search a column of dates then open new worksheets according to the months that are present in the column of dates. So, for example, if the column has dates ranging from January to June, I need the macro to open 6 new worksheets and label them January, February, March, April, May and June.

View Replies!   View Related
How To Generate A Number?
here it is..

500 to 599>>>> equal t0 600
600 to 699>>>> equal to 700

the numbers from equals is the scale of the drawings which i use plot.

sample:

32039.98
31959.50 ...

View Replies!   View Related
Generate Report With %
Analysis of Profit is very impt in business , that is why i keep trying to find way to use excel to generate profit report.

I have been trying to solve this for last one month. even when i bath... But no result.

May i ask any one here know how to convert my data to a report using . Below table said every thing.

Project (10)  HIJ1My Raw Data File  2BrandMonthProfit In %3Brand AJan 0730%4Brand BFeb 0750%5Brand AJan 0730%6Brand BFeb 0750%7   8   9I Need Report In New Work Sheet  10   11BrandJan 07Feb 0712Brand A30%30%13Brand B50%50% Excel tables to the web >> Excel Jeanie HTML 4


View Replies!   View Related
Generate A Word Doc
I have a macro here that is not working becuase User-Defined Type not Defined. I think i understand it but i want to make sure and if there is any advice for the macro below even better.

what is in red below is what i think i need to identify as Type /end Type.

these are named cells from my data page.

Sub Makeworddoc()
' create a word doc
Dim wordapp As Object
Dim data As Range, message As String
Dim Notes As Integer, i As Integer
Dim Participant As sting, POC As String, phone As String, Email As String, Staff As String
Dim Contact As String, When As String

The rest of the macro is

' Collected information from worksheet
Set data = Sheets("data").Range("A13:G13")
Notes = Sheets("data").Range("notes")
' updated status bar
Application.StatusBar = "Creating Word Doc."
' assign current data to variables.............

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