Formula To Set 3rd Thursday Prior To 1st Of The Month
Jun 18, 2014
I am mailing a letter on the 15th day and the 45th day after a hire date. The effective date will be the 1st of the month following 60 days. A meeting will be held every 3rd Thursday of the month for all new hires. What I am trying to accomplish is calculating the 3rd Thursday of the month PRIOR to the 1st of every month.
i.e. hire date is 3/11/14 - 1st letter goes out 3/26/14 (15 days) - 2nd letter goes out 4/25/14 (45 days).
Their eligibility date is 6/1/14 (1st of the month following 60 days).
The meeting is held on 5/15/14 (3rd Thursday of the month prior to the first of the month (June))...
HIRE DATE LIST.xls
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
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.
I need a formula to find the delivery date for an order. If the order is placed by Friday, it will be delivered on the following Thursday as long as it isn't a holiday when it would be the following Friday. I have the holidays listed in a range of cells I2:I5.
I am trying to write a macro to send mail on every Friday and also on last day of every month. If the last day of the month falls on Saturday or Sunday then the macro should mail on Friday. I have written a separate macro to send a mail. I have also written to check day(ie Monday, Tuesday etc) of today. If today is Friday or month end i can send mail. I dont know how to tell the macro to send mail on friday if the month last date is saturday or sunday.
Sub done() Dim Dat As Date, x As Integer, y As Date, sorry As String Dim str As String sorry = "Today is not friday or month end. So i cannot send mails" str = WeekdayName(Weekday(Now()))
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 ...
The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.
For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2
That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.
Is there a way to reference the prior worksheet without using it's specific name? =C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.
I'm trying to create a report that will include current month and previous month by territory. Unfortunately, i'm getting the same values for both months.
PriorMonth=CALCULATE([Sum of TotalDue]),DATEADD(Date[Date],-1,month)) Date =Date table ( Date, Month, year) Relationship= Date.Date=InternetSale.OrderDate Current Month=Sum([Amount])
I have log data in two columns: Column A: Date/time (at 30 minute intervals) Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
I have class monday, tuesday, wednesday & friday but no class thursday. How can I make automatically fill in dates for weekdays only and skip thursday using excel 2013's "Fill" function then "series" ??
I have a macro that when you run it, it creates a new tab with the next month on it. I.e. I'm in January 2014 and I hit the button it creates a tab for February 2014....
In a different workbook, I want to have it copy this newly created tab name into cell A1 of what we'll call Hidden_Tab.
So I want to have Hidden_tab A1 say January 2014, then I press the button to run the macro and it then copies the new tab name from the tab I just created: February 2014 and pastes it in Hidden_tab A1 and so on...
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
Could it be possible to "sequentially offset" a vlaue, say, of A1 such that, for instance, when A1 changes from "5" to "6" (via formula, not direct input), A1=6 and, say, B1=5, and so on? You know, like keeping a value record of A1, one step back, in B1.
This would be UNBELIEVABLY valuble to me. Thus, would I be in eternal debt to he/she who would resolve this here.
I have a need to simply rename a workbook without saving. I have a master template named "IR QT" but, within the workbook, I run a macro that saves the file with content from a cell as the filename. After the 'Save' macro is executed, the sheet stays open, as planned, but obviously the file has been renamed. What I would like to happen at the end of the 'Save' macro is for the sheet to be renamed (not re-saved) to "IR QT". I just need the VBA code to put at the end of the 'Save' macro to do the rename back to "IR QT".
I have a macro that selects various cells whilst it runs - nothing unusual there! What I'd like it to do is return to the activecell immediately prior to running the macro. So if cell 'B34' is active and then the Macro runs it currently returns to cell 'A12' (the last selection in the macro).
I would like it to return to cell, in this instance, 'B34'. The activecell could of course be any reference within the spreadsheet, so whatever is active prior to running macro is returned to when macro is complete.
My Financial year runs from Oct to Sept. My current month is June 2008 and the YTD Expenses is in Cell P2032. I have set up a formula to calculate the prior YTD Expenses as sum(AU2032:AM2032) This is for the period Oct 2006 to June 2007. When I do July 2008, I would like to the formula to caculuate the prior YTD Exp as sum(AU2032:AN2032)
How can I get the formula to Change Automatically based on the Month & Year as per the example shown above?
I have checked the following reference whithin Excel VBA's references Manually:
Microsoft Visual Basic for Applications Extensibility 5.3
BUT when I run the following Macro, once checked the prior reference:
Sub AddModuleToProject() On Error Resume Next Debug.Print Err ' I GET 0 Dim VBProj As VBIDE.VBProject: Set VBProj = ActiveWorkbook.VBProject Debug.Print Err ' I GET 1004 Dim VBComp As VBIDE.VBComponent: Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule) Debug.Print Err ' I GET 91 VBComp.Name = "NewModule" Debug.Print Err ' I GET 91 End Sub
I have attached an example spreadsheet that shows my problem. Basically, in column A I have several thousand rows of 3 or 4 letter codes. In column B, I need a formula that will find one particular target code in Col A and then in the adjacent cell in Col B, display the first code to appear in Col A above the target code which matches the list of desired codes.
So the briefly re-state the problem, I need a formula which finds every case of one particular code in Col A (in the example spreadsheet the code is ABCD), and then read back up Col A to find the first value which matches the desired code (from a list of about 5-10 codes), ignoring other values which represent codes that are not on the list, and place this col B adjacent to the target code.
I have 11 months of sales commission data, and need to estimate the value for December. However, the catch is, for the first 7 months, the values are significantly higher than the most recent 4 months. I'm currently using the TREND function to guesstimate the December value, but with the wide fluctuation between the 2 time periods, I'm thinking the result of the TREND value may be way off.
Is there a way to 'weight' the data to reflect the higher values earlier in the year, with the much lower values later in the year?
I have created a USERform that requires a user to enter data on the form. The Form also has Text boxes that are used for calculated totals. The problem is that as I tab through the boxes that need to have values and input the values. The Calculated field do not get updated until I export the data to the worksheet. Which is ok but I would like to preview the form with the calculations prior to the export. How I can see the Form updated form priorto export I have included my code, when I click Private Sub CBTN_OK_Click() do I see the Form updated but it has already written it to the worksheet
Code
Private Sub CBTN_Cancel_Click() Unload Me End Sub Private Sub CBTN_Clear_Click()
My goal is that the formula in this named range references the prior 12 months. This does reference a twelve month range in my workbook, but not the prior 12 months based on this month being MARCH.
