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


Advertisements:










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

Sponsored Links:

Related Forum Messages:
If 1st Wednes Day Or 3rd Of The Month Then Hightlight The Celll
looking for a formula that will tell me if a cell containing a date
is the 1st wed of the month or a 3rd wed of the month then
hightlight the celll

example
=if(A1=1st wed or 3rd wed,A2=green,"")

View Replies!   View Related
Payments On Second Tuesday Of Every Month
I am wondering if anyone could possibly help me figure out an excel function that would allow me to make a list of payments by year (from 2008 to 2015) that are received on the second tuesday of every month.

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
Format A Number As 1st, 2nd, 3rd Etc.
I cannot figure this out, but does anyone know the custom format code to do a number into 1st, 2nd, 3rd, 4th, 5th, etc.

View Replies!   View Related
Find 1st, 2nd And 3rd Place...?
We're doing a "Biggest Loser" competition at work. I have a spreadsheet that calculates the % of body weight lost. I need a formula that will look at the results and display 1st, 2nd, 3rd places. Here is this weeks results...

View Replies!   View Related
Take The First Letter From The 1st, 2nd, 3rd Names Of The List
I have a few names in the range A2:A11 and the exercise asks: Fill in the cell B19 with a temporary software name. Using the appropriate function, compose such name as follow: take the first letter from the 1st, 2nd, 3rd names of the list; take the third letter from the 4th, 5th, 6th names of the list; take the last letter from the 7th, 8th, 9th, 10th names of the list. Moreover, the software name must be in capital letters.

View Replies!   View Related
Locate & Return The 1st Or 2nd Or 3rd Value
I have one column of data (numbers). Within this column, are the values for two different categories...which are separated by a blank row. And then a total for each product given one column over; like this:

Product1 123
Product1 456
Product1 789 1368

Product2 456
Product2 123
Product2 456 1035

Either product can increase/decrease in rows, so I am unable to set a static range. The only solution I got on my own was to use SUMIF (sum the column of values if it matches the name "Product1" or "Product2"). This is fine until someone misspells the product name or leaves it off completely; which is why I prefer to go off the actual values.

My 1st preference is to go off the totals. Basically, return the 1st value (ie 1368) in the column, and then in another cell, return the 2nd value in the column (ie 1035).

My 2nd preference would be to sum the individual values in the column until a blank cell is encountered, and then return that value. Then continue on until another blank cell is encountered, and return that value as well.

I know very little of vba, so I haven't taken that route, but I bet this is easily done as a UDF (although I would prefer not to do it that way).

View Replies!   View Related
Calculate Sum Between 2 Dates And Equaling 3rd Value
I have a countif formula that calculates the number of entries between 2 dates. However i now need to also count how many of these match a 3rd value.
So i will need to work out how many "are greater than or equal to B2" "less than or equal to C2" and "equalling D2"

what i have already
=(-COUNT('Current Work'!AE8:AE2000)+COUNTIF('Current Work'!AE8:AE2000,">="&B2)+COUNTIF('Current Work'!AE8:AE2000,"<="&C2))

View Replies!   View Related
Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year

Column A is for date input and the same date can be repeated several tumes :-

1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09

Sometimes there are all 30 /31 days but normally not .

I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.

View Replies!   View Related
Using Offset From Latest Month To Calculate 3-month Average Within A Range
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).

Jan-09

Feb-09

Mar-09

Apr-09........


View Replies!   View Related
How Can I Find My 1st Of The Month
am working on a time sheet project. i'm now working on the monthly holiday planner.
Am looking for a way for a formlua to look at 2 rows and return the first of the month i.e 01-01-2009

The rows which need to be looked at are the first two rows of any given month because the 1st of the month will always be in the first two weeks of a month.
my rows are

1st week to look in. b4-h5
2nd week to look in. b19-h19

Now b4 as a paste link in it ..=roster!$H$2 which is the start date of the roster
the result of the search is then put into b2 as Month only ie. January

View Replies!   View Related
1st Monday In The Month
I am currently looking for a formula that will give me the actual date for the first Monday of the week.

I have for example in column A dates from 1st Jan 06 to 31st Jan 06 I just need to workout what the date is for the first Monday then after that for the 2nd Monday it would just be the 1st Monday +7.

View Replies!   View Related
Always Return 1st Of The Month In VBA
From the code below I need to translate whatever date is input to the First day of the month to pass into my VBA via the variable "SMth"

e.g. entered 12-03-09, returned 01-03-09

SMth = InputBox("Enter date of FIRST month ", "Format like 01-01-07", "01-01-07")
SMth = "=DATE(YEAR(SMth),MONTH(SMth),1)"
Cells(3, 8).Value = SMth
The line

SMth = "=DATE(YEAR(SMth),MONTH(SMth),1)"
is giving me an error, what should that line of code be?
OR perhaps you have another solution to reach the same goal


View Replies!   View Related
Change Dates To The 1st Of The Month
I am working in a large database and trying to change the date field. I want to change all the dates to the first of the month.(ie 10/19/2008 to 10/01/2008) Is there a way I can change this in excel without having to go in and manually change all the days to the first.

View Replies!   View Related
1st Monday Of A Month In Date Range
to determine the number of 1st Mondays or any weeday e.g. 5th Fridays between a date range e.g. 1/1/2006 - 4/1/2006.

I've noticed a lot of threads for calculating just one month but none for a period of time.

View Replies!   View Related
Automatically Open A New Worksheet Every 1st Of The Month
I have been trying for the past four months now to get my workbook to automatically open a new worksheet every 1st of the month.

but I have failed each and every month. I have asked others to assist but to no avail.

this is the code in my workbook as it looks now.

Dim strValue As String
Dim Sht As Worksheets, foundSheet As Boolean, TEMPLATE As String
Dim rng As Range
Dim irow As Long
Dim myDate As Date, newDate As Date, oldSheet As String, newSheet As String
Dim ws As Worksheet
Dim strName As String
Dim wsh As Worksheet

i keep getting an error on the activesheet=newsheet.

View Replies!   View Related
Add Income Only Between 1st And 15th Of Each Month
I am a car salesman and made a sheet to track my sales from month to month. I would like to make a formula to only add the entries that fall between the 1st and 15th of each month to show me what my check will be for the first half of the month. Column A has the dates and column J has the amount. I need to calculate only the first half of the month in J24 to know what my mid month check will be......

View Replies!   View Related
Sum By Specific 1st Day Of Month
I have 365 days of data that I need to sum by specific weekday. I need to Sum every fisrt Sundays of each month for the year, then each mondays, Tuesdays, etc. I looked all over the forum and was not able to find a thread. Is ithat possible to do?

View Replies!   View Related
Macro Or Formula That Will Sum On 1st Day Of The MOnth
after HOURS of trial and error. I was able to figure this out and get a formula written that would do what I needed. I'm trying to write a macro or formula that will sum the following

View Replies!   View Related
Lookup Results For 1st & Last Day Of Month
I have a series of daily data in columns sorted by date (see attached .xls).

I am trying to create a table that extracts the value on the last day of a given month, and of the first day of that month, for each of the months in the data series.

I have been trying to get lookups and match functions to work but to no avail.

View Replies!   View Related
Matching 2 Fields In 2 Columns And Copying A 3rd Field Into The 3rd Column
I need a formula. Probably Vlookup. I'm having a LOT of trouble trying to figure out how to "phrase" the formula so let me explain what it is I'm trying to do. If a cell in Sheet 1 Column AS and a cell in Sheet 2 Column B match exactly, I want the cell in Sheet 2 Column C that is in the same row as the matched cell in Sheet 2 Column B to be copied into Sheet 1 Column BB in the same row as the matched cell in Sheet 1 Column AS.

For example, if cells in S1 Column AS and S2 Column B both have the name "LOS ANGELES" then "KLAX" will be copied from cell in S2 Column C (in same row as "LOS ANGELES") to S1 Column BB (in same row as "LOS ANGELES").

View Replies!   View Related
Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month
how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:

If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008

Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.

View Replies!   View Related
UDF To Calculate The Month
I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan
06/02/07 - 05/03/07 = Feb

View Replies!   View Related
Calculate Interest Per Month
Sample data:

02-06-09 to 18-06-09 earned $2500
19-06-09 to 23-08-09 earned $3500
24-08-09 to 31-12-09 earned $4500

I am thinking if there's a formula that can calculate the interest earned for each month from Jun-09 to Dec-09.

View Replies!   View Related
Calculate Fiscal Month
I'm looking for a way to calculate (name) a fiscal month based on a date range. Example attached. Columns A & B are a date range and column D is the desired outcome. I'd prefer a formula solution, but am open to anything as I haven't a clue how to begin with this.

View Replies!   View Related
Auto-calculate Last Day For A Month
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.

Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?

IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".

The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).


View Replies!   View Related
Selecting A Month To Calculate Invoice
Ive created an invoice. The data is being entered on the previous sheet & then this invoice reads the data from the previous sheet through the formulas as shown below. The data that is entered, is entered contineusly everyday, so the invoice calculates the amounts from the entire data input sheet. I need to add some controll to the invoice where I can select a starting date & an ending date, and then the amounts must be calculated on the invoice according to the selected dates.

H I J
27 Qauntity: Cost: Amount:
28 55 R 185.00 R 10,175
29
30 Net amount: R 10,175
31 Vat: R 1,424.50
32 Amount Due: R 11,599.50

Invoice
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
H28 =SUM(Divenic!G5:G5004)
J28 =SUM(H28*I28)
J30 =J28
J31 =J30*0.14
J32 =J30+J31

[Table-It] version 09 by Erik Van Geit

View Replies!   View Related
Calculate The Quartiles With The Condition Of The Year Or The Month Or Even Both
i have a dataset which is like a timeseries with 3 columns
first is year second month third values
so lets say like this

year month day value
2004 12 29 100
2004 12 30 200
2004 12 31 300
2005 01 01 50
2005 01 02 60
.....

I need to calculate the quartiles with the condition of the year or the month or even both... I suppose i need something like an array but i couldnt make it work untill now.

View Replies!   View Related
Calculate Year To Date Based On Known Month
I want to calculate Year To Data in B1 based on some data in C1 to N1. The monthnumber is located in cell A1.

There is of course several ways to do this, but is there a simple and easy formula one can use.

View Replies!   View Related
Auto Calculate The Sart And End Of Each Month
I have a workbook where I would like to enter the date of the starting month when the workbook is being used, and I want it to calculate in the next cell across the ending of that month.

View Replies!   View Related
Calculate Working Days Left In Current Month
Is there a formula to calculate the working days left in the current month? I work in the financial services industry and am putting together a spreadsheet to automatically calculate sales production needs per "working day", based on my businesses schedule.

We are open M-F and Saturday, not including holidays. Now, I can look at a calendar, count the days left and put them in to the spreadsheet, but I'd prefer to have it calculate automatically.

On my spreadsheet, I have used the =TODAY() formula. In the cell below, I would like to have it calculate the actual working days remaining in the current month.

Since today is Sunday August 19th, I know that there are 11 working days left (starting Monday 08-20-2007). Is there a way to get Excel to do that?

View Replies!   View Related
How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %

View Replies!   View Related
Formula To Calculate Date End Of Prev Month
I have a worksheet where the current months date is entered in cell E1 (format dd/mm/yyyy) , using an input box.

I need a formula the will calculate the previous months month end date in cell A6 and the month end prior to the date calculated in A6 in cell A13.

where the dates start with 31 for eg 31/01/2008 , then my formula works, but if it starts with 30, 29 or 28, then obviously it does not work.

I would like a formula that will calculate these previous month end dates correctly.

See example below ....

View Replies!   View Related
Calculate Number Of Dates Within A Column Based On Month
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.

So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.

View Replies!   View Related
Date Function Query (calculate The Last Day Of The Month)
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank
=IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)

The last day of the month function is shown below
=DATE(YEAR(A13),MONTH(A13)+1,0)

This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.

View Replies!   View Related
Auto Calculate End Date Of Previous Month
I have a cell in which I will input the last day of a month (e.g., 1/31/09, 2/28/09, etc.) In another cell, I want Excel to show the last day of the previous month. Is there a formula to calculate this automatically?

I enter in Cell 1: 1/31/09
Excel calculates in Cell 2: 12/28/08

I enter in Cell 1: 2/28/09
Excel calculates in Cell 2: 1/31/09

View Replies!   View Related
SUMIF Function: Calculate What Is The Total For An Individual For The Whole Month
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.

View Replies!   View Related
Calculate The Total Time Users Spend On A Spreadsheet Per Month
I have a simple VBS script that puts the username & current time in columns. When the user saves that time is also placed into a column.

I would like to be able to calculate the amount of time a user has spent on the spreadsheet for the current month & if possible the total time all users have spent on the spreadsheet this months.

View Replies!   View Related
Calculate Future Dates On The Business Day Preceeding The 15th Of Each Month
Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me: =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)

This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th. FYI: I am a n00b..

View Replies!   View Related
Calculate Work Hour Phone Time By Day, Week & Month
I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.

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
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
Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View Replies!   View Related
One Sheet Saves A Column To The 1st Empty Row On A 2nd Sheet After 1st Looking For A Match On An ID Variable
I am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work.

Code:....

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
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 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
Copyright 2005-08 www.BigResource.com, All rights reserved