I have a report at the moment which shows me the calendar year that an action was carried out in, what I would also like to show is which of our financial years (they run April to March) the action was carried out in.
View 7 RepliesFinancial Year ended on 31st March. If date is 18th August 2008, Then last date for financial year is 31st March 2009. IF date is 15th March 2009, Then also last date for financial year is 31st March 2009. I want formula / function to get last date of financial yera.
View 2 Replies View RelatedI have created a monster formula that extracts the Australian financial year (FYR) from a date string and would like to convert the formula to a nice tidy function.
the formula works on a date string with the following format: yyyy-mm-dd (2008-12-15)
for example the formula result for 2008-12-15 is 08/09 (yy/yy)
=IF(OR(LEFT(ExDate,1)+13,MID(ExDate,5,1)"-",MID(ExDate,8,1)"-",LEN(ExDate)10),"Change the expiry date format to yyyy-mm-dd",
IF(OR(MID(ExDate,3,1)+1=1,MID(ExDate,3,2)+1=11),
IF((MID(ExDate,6,2)+1)
I'm looking for a VBA code which will review the date in column C, which is in the format DD/MM/YYYY, and convert it to a financial year in column D.
So... given a financial year April to March what i think i'm looking for is...
If the month is 04 to 12 then column D should display = Current Year / Current Year + 1
If the month is 01 to 03 then column D should display = Current Year - 1 / Current Year
I need column D to be in the format XX/XX ie 10/11 for the period April 2010 to March 2011. I need the code to work from row 2 to the last used row based on data entered in column E.
I have a spreadsheet that consists of date, product number with amount received against each date.
What I need are formulas that will automatically provide me with:
1. The sub-total monthly income for each product against each financial year.
2. Total income for each product by financial year.
3. Total income for each financial year.
How do I calculate/display the financial year for a given date? For instance if my source cell has 25/11/2006 how do I get the result cell to show 2006/07 (or 06/07, 2006/2007, etc).
I've tried a Text() function but the best 'format_text' I could come up with was "yyyy/yyyy+1", which not only doesn't work, but would be wrong if the source date was in the second half of the financial year.
I want to create 26 - 27 folders (depends on financial year)
Basically in the current folder I want the folders to be created in the following format FE 22-04-09 for each fortnight in the financial year.
I have a sheet with following columns
Zones Month Revenue
Zone1 Apr-05 1111
Zone2 Apr-05 2222
Zone3 Apr-05 1222
Zone1 May-05 1451
Zone2 May-05 452
Zone3 May-05 4622
Zone1 Jun-05 891
Zone2 Jun-05 255
Zone3 Jun-05 1555
and so on all zones revenue each month.
I am learning Pivot table and struck at a point.
I am able to do simple pivot table but now want the following result
1. All the three zones in row fields.
2. Months in column field.
3. Revenue and running total of revenue (progressive sum) for all the months of desired financial years. ( I can get revenue and running total separately but I want these in alternate columns ie Revenue Apr-05, Revenue upto Apr-05, Revenue May-05, Revenue upto May-05 and so on)
Also how to get data for desired financial year only as 2005-06 (Apr-05 to March 06) or 06-07 (Apr-06 to Oct-06)?
I have just finished sorting my anniversary list by Month (in alpha order)
B C D E F
1 Hire Date MonthDay YearLast Name First Name
2 04/04/2011April 042011Emp 1
3 04/09/1996April 091996Emp 2
4 08/02/2012August022012Emp 3
5 08/09/2004August092004Emp 4
6 08/13/2001August132001Emp 5
7 08/16/2010August162010Emp 6
Is there a way to sort by Calendar year? Jan, Feb, Mar, etc.
I have the following code in my calendar initialize: ....
View 9 Replies View RelatedI want to find the average value for each Jan 1st, 2nd, 3rd ..., for a data set of five years. How do I do this quickly? I can filter, but would have to do that 365 times and involves a lot of pasting. I tried a pivot table, but basically came down to the same problem, although I don't understand pivot tables that well.
View 9 Replies View RelatedI' using 2007 I'm trying to make a calendar that you can change the year in a text box e.g. 2014 - 2015 and it will change the date and days in the calender rather than having to do it manually.
Is there a way to make this formula refer to a cell rather than having the specific year in there?
=TEXT(WEEKDAY(DATE(2014,1,15),1),"aaa")
I'm using this formula from
[url]
Nth Day Of Week For A Month And Year
This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.
=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))
Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.
I would like to be able to change the year and month in A2 and B2 and have the calendar change.
I will be inserting rows between the weeks to return appts, if I can get this part working.
I could make a new tab for each month, but I thought I would give this a try....
I'm having a data only pull week number and year. We are using Fiscal calendar starting in July. For example, A1 = Week number and A2= Year. How to set up a formula to retrieve a date for this? If A1 = 2 , A2 = 2013, the date will be 07/14/2012. I want the date pull of on Saturday every week.
View 6 Replies View RelatedI am needing to create a formula that will calculate the number of paychecks and the dates for the calendar year. There is some employees that are paid bi-weekly and some bi-monthly.
For Example:
A1-Employee, A2-Hire Date, A3-Pay Frequency, A4-Number of Paychecks
Based on the hire date and pay frequency it will calculate the number of paychecks in A4.
Is this something that can be calculate with excel or would it need to be done manually.
How do I limit the user from being able to choose only 1 year after they pick the first date on the first command button?
Here is the code on a userform which has a Calendar Control ...
I am writing a formula that will sum data for a 12 month period that is not based on a calendar year. For example may have data starting in June 2011 running to date, but not every month. If I set a start date for Year 1, say April 2012, I need to see Year 0 numbers (anything prior to April 2012), Year 1 numbers (April 2012 to March 2013), Year 2 numbers (April 2013 to March 2014), and so on. Date format is currently mmm-yy.
If this could be performed via a macro which references the database (spreadsheet), then even better. The database will contain multiple columns, of which two will contain the date and corresponding number respectively.
I have a spreadsheet that tracks “points” for hourly associates on a daily basis that totals weekly and monthly. This spreadsheet works on a rolling calendar year instead of a fiscal and I need to be able to capture the totals for periods of time by days.
For example, I would need to view how many points ‘employee x’ has from 2/25/09 through 2/25/10. I have attached the spreadsheet, which includes tabs for each week ending and a summary page.
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
I have a program where I can update the calibration due date of an item. I have attached a cut down version of my program showing the relevant areas. There is usually password protection on the worksheet so it can only be edited via the form (the vba coding removes the password protection before editing, then re-enables the password protection after editing). The "Update Calibration" button is usually on a "Menu" sheet.
Once the form is opened a serial number is typed in the textbox. The calendar button is then clicked, which brings up another form with the calendar on. The due date is selected on the calendar. When "OK" is clicked, the date label caption is then changed to the selected calendar date. When "Submit" is clicked, the spreadsheet will search for the Serial Number, once found, the label caption (being the date selected) will be entered into the cell to the right of the serial.
If the day selected on the calendar is greater than 12 the date is entered correctly onto the sheet. example: calendar date selected = 15/01/2010. shown on sheet as 15/01/2010. However, if the day selected on the calendar is 12 or less, the date is for some reason entered incorrectly onto the sheet. example: calendar date selected = 08/12/2010. shown on sheet as 12/08/2010???? What is going on here? how come the day and month are swapped around if the day is less than 12????
Using Excel 2007 Calendar Control:
I want to select a date of choice from a calendar in a field within an Excel spreadsheet.
I do not know how to do userforms and so that is why I wonder if I can add this in just a spreadsheet.
This is how far I have gotten so far: After doing the following, I have a static calendar setting in my spreadsheet like a text box showing the current date.
Developer
Insert
More Controls
Calendar Control 12.0
I am using a great pop up calendar without ActiveX made by a guy called Phil Johnson. When you click a specified cell the calendar appears. The only problem is that the calendar doesn't default to today's date when it opens.
I have the original file Calendar source code if anyone needs it.
I would like a user friendly form where I could enter
£50 15% discount and it would add 50% plus VAT and produce POR & POC which would adjust if the suggested price was changed.
I work on graphics which show financial data. The base is day data together with calculated added values the graphic worked and showed good pictures.
But now I encountered a problem with the graph - related to not listed days, points are "generated" which do not be in line with the rest of the data !?
EXCEL_Forum_20140118.jpg
i have to compare two financial reports from two different sources each and it should derive the same result.
I am still working on it and only focus on Column C & D. However, i have come across some problems and hope that these milestones can be cleared so that i can move on to another step to achieve my goal.
So, my current problem is
1) #N/A (eg Cell D9 & D32) will be resulted when hlookup function is used. Since the workbook 'CB_Consol_2009' does not have the same name appear, how can i make the answer become '-' instead of #N/A when hlookup function is being used?
2) Double counting. The same name appears more than one time (eg CURRENT ACCOUNTS-RET_ (01#0505_01) and as such, its figures also calculating more than once. Is there a way to calculate the figure only once so that i come out with the correct grand total?
As my objective is to compare two financial reports results (which should be the same), this is the table i can think of. If anyone here have any constructive comment on my current format.
I have a tab that has rows with line items from a financial report (the items start with unique identifier numbers) and the tab also has historical monthly data for each of these items. I now have a new tab with the same line items but they are now in a different order. Is there a formula that I can use to copy all of the data from the old tab into the corresponding monthly line item in the new tab? Nothing changes except for the order of the rows.
Also, at the top of the old tab, there are totals that grab from each of the rows below. If I copy these formulas into the new tab, I think that they will reference the incorrect rows (as the order has changed) Is there any way to transfer these formulas and keep the linkage intact?
In my efforts to improve worksheet readability across the board at my work I am researching spreadsheet standards. I have found the Spreadsheet standards review board http://www.ssrb.org/index.html that has a massive document containing valuable standards to adhere to for financial modeling. Since I learn best by example, I am wondering what standards any of you guys are using and if possible post a sample spreadsheet containing those standards.
View 3 Replies View RelatedIf today = any date in November, how can I display in a seperate cell that that is month 8 of an April - Mar Financial year.
A4 =Today() 18/11/09
a5 = can this display as 8 and then 9 when a4 becomes a december date?
Excel formula? What I would like to get is a period number for financially month - not sure if it is possible
For example: 29, 30 & 31 of July suppose to be period 8.
I'm researching a load of financial data downloaded for me from Bloomberg. It lists daily prices of 15 commodities for nearly 20 years in some cases! Its far too much data to get anything worthwhile out of, so I'd like to know if any of you guys have used or know what the VBA is to write a macro that will reduce all the data from daily to monthly. (ie. I want a macro that will allow me to quickly delete all data corresponding to dates which are NOT the end of the month).
View 5 Replies View Related