I've been heavily updating my Budget file I've written in excel. The code I'm working with and having slight trouble with is as follows
Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
 Z100 =IF(TODAY()>DATE(2013,1,31),"January's Ending Balance is:",IF(TODAY()<=DATE(2013,1,31),"January's Current Balance is:"))Â
Basically, if the current date is after the last day of a given month (in this case January 31), then the cell should display "January's Ending Balance is:", if between January 1, (current year), and January 31, (current year), it should display "January's Current Balance is:". The code above does work great, but I need it to look at the current year according to the computer's date, and go by that, instead of having to change the code where it says 2013 to 2014 etc every year. This would be a hassle, as I have a tab coded for each month of the year. Id rather it be automated.
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then MsgBox ("You may not enter Data before the current Month") Else '...... Run main code here
Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.
Month 2012 2013 Jan 300 100 Feb 275 100 Mar 400 150 April 650 May 454 June 800 July 500 Aug 375 Sep 525 Oct. 300 Nov 410 Dec 510
Sample file is attached : Comparison_Years.2011.xls‎
I am working on a budget for myself and want it to have running dates so the first data column will have the current month. I was able to succeed with this using the EOMONTH function followed by EDATE functions in the following cells, I then have these columns filled using a nested VLOOKUP MATCH function pair.
The problem I run into is with the months that extend into the next year, in my data table I have month by month listed started on 01/01/2014 ending 12/01/2014 but as soon as the month is no longer January the last column in my budget cannot find the information needed due to it looking for 2015. so what I would like to know is if there is a way to make the data table change the year to the following year after today is beyond that month, so for example on March 1 2014 both January and February would be changed to 2015.
I have dates in my column “A”, for example (A1 cell =22-Mar-1971), (A2 cell=30-Dec-1965). Now my requirement is in B column date and month from A column and year should take current year. Output in B column (B1 cell =22-Mar-2009), (B2 cell=30-Dec-2009)
I am trying to do an IF statement, if the activecell's value is equal to this current year, do nothing and if its not the current year, to offset one column to the right and insert a blank column, then copy the whole column to the left and replace it with the new blank column.
I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.
1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …
2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?
I have a file which I use for planning purposes, sometimes two or three years ahead of time. I have a "Reference_Year" cell, B1, which controls most of the dates in the spreadsheet.
I have a code which is intended to change the current year of Excel so if I have 2016 as the "Reference_Year" and I enter "05/05" somewhere in the range D5:D50, the date will appear as 05/05/2016. I feel like I am not too far but at the moment the date I get is 05/05/2000. Ideally I would like it to work with a Reference_Year entered in the format 01/01/2016 (which is used for the rest of the spreadsheet).
I am a memeber of this forum for more than a year, and currently assign into a new assignment which dealing an excel file everyday. We have one excel file for moniroting of action items generated by the management after the study. As since there were around 2,500+ rows has been generated since in the beginning of 1990's till todate. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this month out of the total numbers of action items.
Is it possible to use the COUNTIF formula to count the number of items in Col C, where Col B contains a date?
I imported a large amount of data from another program. The data is in one column and is in date format (02/24/57), for example. The data is made up of my clients birthdays. I want to import those birthdays into my outlook to create reminders but the dates are years past. I need to change the birth year to the current year in order for the reminder to activate going forward. If I dont update the year, the dates import back to the date they were born. In some cases 60 years back. I am hoping that I dont have to manually change each date before I attempt to import into outlook.
I currently have a spreadsheet with 500ish renewal dates. I need to be able to take the renewal dates (dd-mmm) in column B and then display the current year with renewal date, if the date has not yet come, or the next year with renewal date, if the date has already passed in column C (dd-mmm-yy).
I've got these inventory numbers that I've been working with.
Last years Usage and this years usage combined per warehouse for each item.
Plus another sheet that has On-Hand counts per warehouse for each item.
What I'm trying to do is pull my usage count per item per warehouse and place it all on different sheets so that I can compare numbers and do a few different things.
On my data sheet I have the warehouse number, in my example, "TOT.", 0100, and 0101. In the next column is the item # and on the end is the total used for that item by that warehouse.
On my the individual warehouse sheets I have the warehouse number, and the item # and some other things.
What I need is someone to look for the warehouse name "TOT.", 0100, or 0101 and then find the Item # and get the total used from the total column. If it wasn't used or does exist then I need 0 to appear.
I'm calculating sick days on an Attendance Report. - Sick days accumulate at a rate of 1.25 per month. - Employees will carry-forward unused sick days from 2008. - The maximum sick days is 30.
In trying to create a formula that will multiple the current month (eg Dec=12) by 1.25, subtract any sick days taken YTD (S16), and add the carry-forward days from 2008 (W16), I noticed one problem with the following formula:
finding a code that would automatically insert various phrases into a column depending on whether or not the person in that row was due to renew their membership in our organization. The code I received works fantastically, but I've come across one snafu:
I'm working on pivot tables using excel macro. Basically , I need to filter out the year submitted according to the current year . I did this to filter out my pivot :
For Each PivItem In _ PT.PivotFields("Year Submitted").PivotItems PivItem.Visible = True Next PivItem For Each PivItem In _ PT.PivotFields("Year Submitted").PivotItems Select Case PivItem.Name Case "2012"
[code]....
But this can only filter out year 2012 . I need to use this workbook for the next couple of years and I dont want to keep modifying the codes .
how to make a date automatically show up in the current year.
For example, if A1 = 6/2/2005, and I want B1 to equal that same month and day, but 2011... How can I do that?
I know =6/2/2005 + 365*6 would work, but the date in A1 will vary... so it has to be a way of showing ANY date with ANY year with the same month/day but current year.
I've got a formula that has numerous nested IF statement and am trying to get the true result to be the first of a given month of the current year. Here's my formula:
Everything works, save that the cell becomes 1/1 with no year at all. I've messed around a bit with TEXT and various DATE/MONTH/YEAR functions on this one, but can't seem to get it correct.
I have a workbook with two spreadsheets in it. On the second sheet there is a large table, which column headings are months and years (e.g. Jun-07 Jul-07, Aug-07, Sep-07....). I was trying to write an excel vba code that would search the first row (column headings) to find the current month and year, and copy the corresponding column along with two previous columns (months) to the first sheet. I would like to have a code that will be able to do this in Jan-08, Feb-08, or Jan-09 as well.
In sub which will convert file to PDF and save it to assigned folder and then attach it to email in outlook. All works fine.
However is is possible to modify the code to save fole to respective month folder ( as per current date and year)?
e.g. I have created folder Named 'Trial' in C drive . This has sub folders 2014, 2015, 2016, 2017, 2018 Each year folder will have sub-sub folders month wise like This has sub folders as Jan , Feb , Mar , Apr .... till Dec Now e.g. if date when the pdf was created is 23/4/14 then it is saved in C:Trials2014Apr automatically. Currently every year I keep creating new folders etc... bit primitive though.
I have a working spreadsheet that utilizes many many formulas in many worksheets. I am utilizing the sumproduct function in many of the formulas. The problem is that it takes forever to run and update this report. I think I can utilize a pivot table except I have one small issue.
I would like to create a Macro that would run through my Details worksheet (contains all of my raw data) and seeks out certain criterias and issues a 2.5 or 3.5 result into a new column. Column C contains Make names. Column B contains Model numnber. I would like the 2.5/3.5 result to be recorded in column Y.
Make/Model Fujitsu - Any model number that starts with MH Seagate - Any model number that starts with ST9 IBM - Any model number that starts with IC2 IBM - Any model number that starts with HT Hitachi - Any model number that starts with DK2 Hitachi - Any model number that starts with HT Hitachi - Any model number that starts with IC2 Western Digital - Any model number that Ends with UE Western Digital - Any model number that Ends with VE Western Digital - Any model number that Ends with BEAS Western Digital - Any model number that Ends with BEVS
If any the the above Make/Model criterias match I would like 2.5 to be recorded in the Y column for the row. If the Make/Model criterias do not match I would like 3.5 to be recorded in the Y column for that row.
Is it possible to utilize the AVERAGEIF function for a specific range of values. For Example, instead of ">45", I would be looking to only average the cells that have values between 40 & 60. I've tried several different ways to input that into the function but nothing seems to work.
