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
 Payments On Second Tuesday Of Every Month
 Return Last Friday From A Given Month
 First Monday, Thursday, And Friday Of Month
 Format A Number As 1st, 2nd, 3rd Etc.
 Find 1st, 2nd And 3rd Place...?
 Take The First Letter From The 1st, 2nd, 3rd Names Of The List
 Locate & Return The 1st Or 2nd Or 3rd Value
 Calculate Sum Between 2 Dates And Equaling 3rd Value
 Last Ocurance Of The Last Date Used For Each Month And Then Use The Cell Number To Calculate The Column Totals For That Month
 Using Offset From Latest Month To Calculate 3month Average Within A Range
 How Can I Find My 1st Of The Month
 1st Monday In The Month
 Always Return 1st Of The Month In VBA
 Change Dates To The 1st Of The Month
 1st Monday Of A Month In Date Range
 Automatically Open A New Worksheet Every 1st Of The Month
 Add Income Only Between 1st And 15th Of Each Month
 Sum By Specific 1st Day Of Month
 Macro Or Formula That Will Sum On 1st Day Of The MOnth
 Lookup Results For 1st & Last Day Of Month
 Matching 2 Fields In 2 Columns And Copying A 3rd Field Into The 3rd Column
 Configuring Dates: Enter A Date In One Column, Another Column Will Automatically Populate With The 1st Of The Next Month
 UDF To Calculate The Month
 Calculate Interest Per Month
 Calculate Fiscal Month
 Autocalculate Last Day For A Month
 Selecting A Month To Calculate Invoice
 Calculate The Quartiles With The Condition Of The Year Or The Month Or Even Both
 Calculate Year To Date Based On Known Month
 Auto Calculate The Sart And End Of Each Month
 Calculate Working Days Left In Current Month
 How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month
 Formula To Calculate Date End Of Prev Month
 Calculate Number Of Dates Within A Column Based On Month
 Date Function Query (calculate The Last Day Of The Month)
 Auto Calculate End Date Of Previous Month
 SUMIF Function: Calculate What Is The Total For An Individual For The Whole Month
 Calculate The Total Time Users Spend On A Spreadsheet Per Month
 Calculate Future Dates On The Business Day Preceeding The 15th Of Each Month
 Calculate Work Hour Phone Time By Day, Week & Month
 Reminder On Every Other Friday
 DATES: Identify Every Other Friday
 Calculating Friday Date
 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)
 One Sheet Saves A Column To The 1st Empty Row On A 2nd Sheet After 1st Looking For A Match On An ID Variable
 Change Nonworking Day To Friday
 Generate A List With The Date Of Every Other Friday
 Formula Return Following Wednesday Or Friday Date
 Change Cell Color If Date Equals To Friday
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
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
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 01012009 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. b4h5 2nd week to look in. b19h19 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 120309, returned 010309 SMth = InputBox("Enter date of FIRST month ", "Format like 010107", "010107") 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
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
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
Calculate Interest Per Month
Sample data: 020609 to 180609 earned $2500 190609 to 230809 earned $3500 240809 to 311209 earned $4500 I am thinking if there's a formula that can calculate the interest earned for each month from Jun09 to Dec09.
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
Autocalculate 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 [TableIt] 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 [TableIt] version 09 by Erik Van Geit
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 MF 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 08202007). 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 Work Hour Phone Time By Day, Week & Month
I am trying to figure out a way to count the minutes used from 8am5pm MondayFriday. 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
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
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
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 052908, a Thursday, is entered in cell F2, I need cell J27 to return the following Wednesday's date of 60408. or For example, when 060108, a Sunday, is entered in cell F2, I need cell J27 to return the following Friday's date of 060608.
View Replies!
View Related
