If / Then In A Macro - Changing 2 Digit Year Into 4 Digit Year
Jul 3, 2014
I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").
I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).
The statement I had was:
=if(A1>=50,1900+A1,2000+A1)
I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.
It seems like it should be pretty simple, but I don't know how to word it in a macro.
I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it.
Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with).
I am importing data from a .csv file. The file contains dates like 02/15/40. The year is 2040, not 1940. Excel doesn't seem to use the Control Panel -> Regional Settings change when I open this file. I have my Regional Settings set to 2000 => 2099 to change to a two digit year. If I open a blank worksheet and type in 2/15/40 in a cell...Excel does the correct conversion to 2040. However, when I open a .csv file it considers it 1940.
This is my code. It is for checking that only a 4 digit fiscal year is entered in a cell. The code works fine except it only checks a max of three time. So if the user enters xxx it will prompt for a correct response three times, if xxx is entere a 4th time then that value appears in the cell. I was wondering why this is taking place.
I have a macro which will import data to the worksheet, then perform some formatting on the data, then assign the month & job description based on the lookup table. The problem is that when I import in the data, the data in column B&C will be store as text instead of number and the date in column E will store a 2 digit year instead on 4 digit year which cause error to my macro. I have try to preset the column format to number, i even try to change the column format to number when i run the format macro data. But the problem is still there.
I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?
How to calculate a check digit in excel. The details to calculate this are as follows:
All variables in the calculation are positive integers.
We take each integer of the pro number and multiply it by a value and sum them to get a total.
An Example: 8 Digit Pro Number: 66988757 Pro Number: 6 6 9 8 8 7 5 7 Position in the Pro Number: 1 2 3 4 5 6 7 8 Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.
Using our example pro again: 6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8) 6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2 54+ 48+ 63+48+40+28+15+14 = 310 Take the Sum of the previous calculation and divide it by 11 310/11 = 28
(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).
Figure a remainder by multiplying the quotient by 11 and subtracting from the sum. Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2 Check Digit = 11- Remainder Check Digit = 11 - 2 ---> Check Digit = 9 Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.
I have a column of 6 digit numbers in excel, and I need to remove the last digit from each number, turning it into a 5 digit number. No rounding, just simply remove the last digit. Each number is different. Does anyone know how to quickly and efficiently remove the last digit from each number? I can convert to alphanumeric string if need be...
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)
In my sheet I have a cell that has the year in 4 digits plus 5 other digits for incidents in our fire dept. (ie 2008#####) what I want is to have the year automatically change to 2009 on the first day of the new year.
I have been using the following to change the year in the Header
Code: Sub Change_Format() ' Ctrl-y will change the year per individual sheet ActiveSheet.PageSetup.RightHeader = Format(Now(), "YYYY") End Sub
Is there a way to have this done automatically? There are absout 6 sheets in the workbook that have the year in the header and right now the code is in Module 3 and I have to press ctrl+y for every sheet.
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 know that in order to draw a chart where a data line for a certain period is compared with the same period the previous year, one should have the 2 sets of data of different year side by side columnwise. However, is there a way where I could still churn out the same line chart when the data is all on a single column?
I have a sheet where column A5 to Axx is filled with date from 01. jan to 31.dec. I.E. A5= 01.01.2008 A6= 02.01.2008 A7=03.01.2008 etc etc
Based on comparing the date the sheets collecects data from other sheets. It works fine, but I want the possibility to change the year by entering i.e 2007 in cell A1 and then the sheets change Cell A5 to 01.01.2007. I know i can use replace function manually, but when i want to simulate/compare betwen years it would be very nice to just enter the year and the sheet would collect new data.
Cell B1 conatins the date 01/01/2007, and cell B2 contains the date 31/01/2007. I repeat this for every month of the year.
I want to be able to change the year of these dates in B1 and B2 from a seperate cell on another page.. so I can change the search ranges to 2008 without going into each cell and changing it manually.
I'm using this formula to count how many times the date in column C and the text "WON" appears in column I and it falls within the month & year that is in Z65.
I have an address (city, state, zip format) in a cell. Some of the zip codes only have a 4-digit zip code which is the way the file was received. I know these zip codes have leading zero. How can I convert these to a five-digit code. Examples are as follows:
Waltham MA 2453 convert to 'Waltham MA 02453'
Boston MA 2210 convert to 'Boston MA 02210'
CEDAR GROVE, NJ 7009-1174 convert to 'CEDAR GROVE, NJ 07009-1174'
TEANECK CITY, NJ 7666 convert to 'TEANECK CITY, NJ 07666'
I'd like to know a formula which can calculate the moving annual total, that is the sum of the last 4 quarters. Now every time the sales from a quarter is known, i have to recalculate the MAT mannually.
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
I have a sheet in my workbook with at least 180 small tables, there may be more. I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.
For example: If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)