I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
I have a table that looks like this (its basically a historical data of a stock exchange):
Date Index January 4, 2010
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range. Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I am attempting to create a formula that will select a value from a lookup table where that value is related to a specific date BUT that date, derived from an input date (any day within a selected month) must always be the first BUSINESS day of that month.
I have a workbook with two worksheets in it.
The first worksheet is the input page.
On that page I want to have a list of dates which display the FIRST BUSINESS DAY of a month - e.g.
Cell A1 - Date entered as 19/9/12 and displayed as 19-Sep-12 Cell B1 - NEEDS THE REQUIRED FORMUAL TO CALCULATE THE FIRST BUSINESS DAY OF THIS MONTH (SEP) (in this example, this should be 3) and displayed as 03-Sep-12 Cell C1 - Will display the value from the lookup table on worksheet 2 (see below) related to the date in B1
The second worksheet is a lookup table containing a sequential list of dates from 28-Jan-69 through to present (19-Sep-12) in Column A.
The adjacent column B contains the related value to that date (the value being a foreign exchange rate) - e.g.
Daily Date USD
29 June 2012 1.0191
1 August 2012 1.0507
Worksheet 1, Cell C1 should display the value of 1.0266
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.
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
I am trying to calculate the Date of the next occurring 3rd Friday of the month in relation to a Static Date in a cell. So if Static Date in cell is Tuesday of the second week of a month, then I am trying to return the date of the 3rd Friday of that month. If the static date in the cell is the third friday of the month, I just want to return that particular date. And if the static date is after the 3rd Friday of the month, I am trying to return the 3rd friday of the following month. So far here is the formula I am using:
The problem I have is that if the static date is after the third friday of the month, this continues to return the 3rd friday of the month of the static date and does not advance to the third friday of the following month. Looks like I cannot attached a sample spreadsheet but here is a screenshot of the sample sheet:
I would like to calculate the week number of the month based on a date.
Now my days would only include working weeks (Monday - Friday).
Supposed the date is 12/31/2012:
M 31-Dec T 1-Jan W 2-Jan TH 3-Jan F 4-Jan
Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:
M 28-Jan T 29-Jan W 30-Jan TH 31-Jan F 1-Feb
It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:
M 29-Apr T 30-Apr W 1-May TH 2-May F 3-May
It will be considered as Week 1 of May since it occupies only 2 days of the working week.
Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.
I have a spreadsheet and every row has an incident date. What I would like to do is calculate which relative month it is compared with today's date. I know how to show if it is in the current month, but what I want is something like the following:
Today's date is 9 July 13
Row 1 date = 9 June 13, hence calculation = -1 Row 2 date = 17 May 13, hence calculation = -2 Row 3 date = 29 Jan 13, hence calculation = -6 etc
Some sort of month to month comparison resulting in an integer.
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name "LARSheet1 to 1" ]
I'm trying to build "Leave, Attendance Management" system using Excel. I am about 40% there so far.
The basic structure of the workbook is as follows:
12x main monthly "Leave Attendance Record" worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ]. 1 x worksheet which holds "data validation" list andvlook up info. ["FormulaListSheet"] 1 x worksheet acting as a database of staff details andpay numbers. ["StaffdBaseSheet"] 1 x summary page which shows staff reaming leaveallocation. ["SummaryLeaveSheet"]
What I'm having problems with at the moment is as follows:
A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).
I would like it to work like this:
When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.
I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.
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).
Is there a formula in excel that will automatically populate a field with the prior months end date? For example - If my spreadsheet is opened on March 25, I would like the date in cell A1 to read 2/28/07.
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 ...
Now, what I need to accomplish is that the D1 and D3's in sheet 2 need to result in a date next to the correct country (the date (in full) must be the first monday of the correct week). I find it quit difficult to do this because in sheet 2 you have once the country name, but several possible dates. So in sheet 1 there must be a date for every D1 or D3 but under each other.
The second problem is that I need to accomplish to get a "x" in sheet 3 under the correct month where there is an D1 or D3 in sheet 2 (week).
So I need to go from a week to a month and this can be for one country 1, 2, 3 or even more months (it depends from the D1 and D3's in sheet 2).
I have a sheet where the columns have the month end date for each date, ex January 31,2014 February 28, 2014, March 31, 2014 etc. These dates are used in another formula to compare to the current date and if the current date is past the result is different from when if the date is in the future.
I would like a formula that would update the Month end date when we enter a new year. So for example once we get to January 1 2015 my date would change to January 31,2015, February 28, 2015, March 31, 2015. this would note be dependent on the system date but on the date in another cell.
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.
i use dates a LOT in my job however if i want to add a month to a date the best i came up with is the following:
BTW all dates are USA standard (m/d/y)
A1 = 12/1/2008 Lets say i want to add 3 months but the day NEEDS to be 1 because of all of my array sumif's are based off of month 12/08, 2/09 etc, and in excel 12/08 actually translates to 12/1/08. So here is the formula I have been using for the last 4 years....
that seems a bit "Clunky" to me anyone have a better idea?
do not tell me "=a1+30" or "31" because that does not work universally.
I would like to create a financial modeling spreadsheet. Basically my problem is this; I want to have a month by month row for 5 years. For the value of each one I would like it to increase by 950 per month automatically.