I have a large data sheet with dates in column B - in column A, I'm trying to write a formula that will determine what fiscal month it should be mapped to.
I have the calendar listed on a different tab.
Fiscal Month
Start Date
End Date
January
1/1/2012
1/31/2012
[Code] .........
The formula I tried using is: =LOOKUP(B2,Calendar!$B$2:$C$25)
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.
set a formula to auto calculate the staggered rent for the month. When I change the date, it will tell me for this month I should charge according to the rates for the year.
Rent for the month Start Date Year 1 Year 2 Year 3 01/07/14 Explanation 01/08/13 10 20 30 10 < 1 yr = 10 01/07/13 40 50 60 50 enter 2nd yr = 50 16/07/13 70 80 90 76.29 (15/31*70)+(16/31*80) 16/07/13 10 20 30 15.16 (15/31*10)+(16/31*20)
I need to convert all these dates to week 1- week 5 week 1: 1-7, week 2: 8-14, week3: 15-21, week4: 22-28, week5: 29-31 Instead of 1/1/2013 it should show week1 instead 1/10/2013 week2 like this
Is there any way to defined our own fiscal month. I have a fiscal year where the start & end date is different from the normal calendar. I have store the start & end date on the different column. What i need is that when people enter a date, it will look up to the table and return the fiscal month.It's something like If the value is >=column A and <=column B, then the fiscal month=column C(refer to the below table). But i have no idea how to make comparison on date value. what kind of formula i should use? And also how to write the code if i want to use macro to implement this?
Start End Month 09/21/08 10/18/08 Oct 10/19/08 11/22/08 Nov 11/23/08 12/27/08 Dec 12/28/08 01/24/09 Jan 01/25/09 02/21/09 Feb 02/22/09 03/28/09 Mar 03/29/09 04/25/09 Apr 04/26/09 05/23/09 May 05/24/09 06/27/09 Jun 06/28/09 07/25/09 Jul 07/26/09 08/22/09 Aug 08/23/09 09/26/09 Sep
I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week
For example: WK 1 OCT FY2013 get converted to Oct 1-7
I have a Fiscal Date macro that will looks at a date to give me the current fiscal month or week, but I would also like to add the functionality for it to give me the quarter.
In my business, the fiscal year starts in October. The quarters of course are as follows:
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).
I am looking to create a function that outputs the upcoming quarter end date based on a specified start date, for which the quarter end is based on a broken fiscal year ending december 15.
As an example, say that you sign up as a customer with an internet provider on 2014-01-01. The internet provider charges all their clients on a quarterly basis and have a broken fiscal year ending on december 15. Hence, as you signed up on 2014-01-01 you will be charged on 2014-03-15, which is the date of the company's first quarter end.
So what I would like to do is to set up a function that outputs the first date I will be charged based on the date that I sign up. If I sign up between december 2013-12-16 and 2014-03-15, the formula should output 2014-03-15. If I sign up between 2014-03-16 and 2014-06-15, the formula should output 2014-06-15 etc. etc.
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 ...
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost Col B = The number of the month when the costs are starting (1=Jan etc) Col C = The duration or the number of months for which the costs are to spread Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I need some help with formula to display a value based upon a certain date. I have a spreadsheet used within a hospital that records the date of a patients death, the calendar year for the spreadsheet begins April 08 and the year is split quarterly as shown below
get the current month name in a Cell A1 and Next month name in Cell B1. what formula should I used? This will be a part of the macro that I'm creating.
The macro will be use every 11th to the last day of the month so I want the formula to be dynamic enough to work in each day the macro will be use.
I am looking for a formula that will select a date in the month based on certain criteria. Found the choose function but not sure if I can really get that to work. I basically have a list of clients, with zip codes, restricted days of the week - and would like the system to group them by zip code and select the best day of the month to schedule an inspection but not pick he restricted day. The goal being have zip codes scheduled together - but on a day other than garbage day. Is this even doable?
On a excel sheet I've got columns, each column represents a weeknumber. I want to calculate the so-called 4 wk average for each row and for each week and this is the formula I use:
(this is not the actual formula but simplified, that's not really important).
It's the checks that make things a bit more complex. If a value of a weeknr is zero, skip it, but if the next value is also zero, just skip the formula alltogether and make it a zero (or text like "false"). So another thing that has to be accounted for is that if a value is zero, the next weeks value is taken instead.Example (see included file):
I want to calculate the formula (mov 4wk avg) for the third value for week 12, which will make the formula
(0.2*6)+(0.3*6) now there's a zero on week 14 so I skip it, then formula will be: (0.2*6)+(0.3*6)+(0.6*6)+(0.9*6)/(6).
Right now I'm doing this in VBA with a lot of variables and a lot of if statements.Is there an easier more effective
I know the example sheet is a 2007/2010 version but I need to accomplish this for 2003.
I have two sets of data tables. One contains Number, Name & Date. The other contains amounts which those people have paid in 2007, 2008, 2009 & 2010. So in the below Jones has paid a total of $580 over those years, Smith has paid a total of $650 over those dates & so on.
The amounts in 2007, 2008 etc are full year amounts. I'm after a formula that can calculate the amounts for part years based on the dates from C1 to C11 by number (or name).
I've got what I think the results should be (I think with some rounding problems), but my real data has much more data.
I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...
January 1 2012 to January 31 2012
The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.
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.
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'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).
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.