ex.
A2 = 10/1/2005: with the formula up there it turns into Fall 2005
i want to be able to add any number of years and the formula will still come up with the quarters system
also i would like A2 to be stationary and create a list of quarters for each year i add on
if there is a simple way to get the GMT equivalent in vba code. I am being a date/timestamp but realized that timezone differences may complicate matters, therefore I would like to "standardize" the timestamps into their GMT equivalent rather then using the computer's local time. I had been using
I've searched the boards & haven't found anything as uncomplicated as what I'm looking for. I know I could use Sumproduct, but for something this simple is there anything I can do with Sumif? This in effect is my worksheet. AB 1AD200=SUMIF('Sheet2’!B:B, A1, 'Sheet2’!C:C)
All I want to do is change the criteria to be the equivalent of A1 OR B2 (ie checking the range for either match. No way to do that in a sumif? I'm looking for the least complicated, easiest to adjust/edit formula possible.
I'm working on a spreadsheet which needs to refer to the current date. My PC checks the system date/time daily. Can I assume that if an end-user regularly connects to the internet, their PC will have a correct system date? Does anyone know if Win2000 or Win98 automatically update the system date/time when connected to the internet?
How do you convert a range of dates in this format: 39843 (i.e today 30th of Jan 2009). To text like: 20080130. Secondly, For the same date, how do you make the weeknum function give answer '05' and not just '5'
I am currently using Excel 2007 and I am looking for an easier way to enter the system date and time into a cell. I am aware of the MS-Excel shortcuts but work in an environment where data capturers still often get it wrong.
Is it possible to use a Excel function/button to auto insert the system date and time by clicking on a cell/button or Alternatively use a calendar to do so.
i woul like to make a macr which will check the system date on the computer to see what month it is and the populate cells A14 - A44 with the dates from the 1st till the end of the month.
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then nh_stringToDate = nh_stringToDate - 1462 End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.
Which filters my database to display only rows in which column J (10) equals "Cows", to display all rows that equal "Cow" and "Chicken" and "Giraffe" and "Cheetah"?
I have a situation where I need to highlight different customers based on what there terms of payment are. Conditions 1 and 3 seem to be working fine but for some reason condition 2 is not working at all. I made sure all names are exactly as listed in the A2 cell....
I'm writing a macro to search for letters A to Z in one spreadsheet, and copy it's associated data into another spreadsheet where A to Z already exist.
What if, one day, "A" does not exist in the source data? If say, the data started at "B", because there were no results for "A". Currently, the macro will stop working if it attempts to search for a value that doesnt exist.
How do I make it, for example, Search for "A", find that "A" doesnt exist, then skip to Searching for "B", and then "C" and so on?
I need to auto fill quarters but Excel fills years instead. For example I write Q1-11 in cell A1 and when dragging down it fills with Q1-12 rather than Q2-11.
I have a list of Dates (01-OCT-2011, 03-Dec-2011, 12-Jan-12, 10-FEB-2012, 03-APR-2012, etc). I would like to figure a formula that would see the the date and return the QUARTER. For example:
IF dates are between 01-OCT-2011 thru 31-DEC-2012 THEN return QTR1, IF dates are between 01-JAN-2012 thru 31-MAR-2012 THEN return QTR2, IF dates are between 01-APR-2012 thru 30-JUN-2012 then return QTR3, etc
I'm really just now sure how to do the "betweens".
I have, for my formula, 2 constant values a and x, I then also have a variable, n. The formula I'm trying to convert into excel terms is SIGMA (with respect from 1 upto n) ((1-x)^(1-n) * x)*a. Unfortunatly thats not drastically inteligable so i've attached a picture file that should explain it in more understandable terms. Now the problem im having is, sigma is a cummulative funtion, which in this example is relative to the variable n, firstly I don't know what formulas I would have to use in order to get a sigma/summination function, and secondly I am uncertain of how to link it to a variable range.
I have created a dictionary with technical terminology. It is in excel format with about 10,000 entries. The format of my dictionary ("Sheet1") is in three columns:
<vocabulary term> <pronunciation> <definition>
Now, I want to create vocabulary lists using my dictionary. The newly typed up vocabulary list (which only has the term, but not pronunciation or definition), will be copy and pasted into "Sheet2".
The "Sheet2" typed up vocabulary list is in the format:
<vocabulary term 1> <vocabulary term 2> etc...
I want to run a macro or script to dictionary terms (including pronunciation and definition) from "Sheet1" and put them into my vocabulary list "Sheet2".
I would like to create a code that would summarize any day of the year in numerical terms.
The formula for coding "days" is as follows:
Coding Month value + current day
Where "coding month" was a combination of the year and month.
The year can be summarized as a fixed value:
2007 yr = 4 2008 = 5 2009 = 6
Ignore anything prior to 2007 and anything after 2009.
The month is simply a SINGLE digit to represent the month and where January=1, feb=2, march=3......Sept=9. Once you hit October, you have to reduce the values to a single digit, so October = 10 = 1+0=1, November =11=1+1 =2, December = 12 = 1+2=3
Now, comes the addition of the individual day to the mix. If you wanted to find the value for May 23, 2007
You know that May 2007 is 5+4 = 9 so adding the 23rd day is 2+3=5 therefore 9 + 5 = 14. Right?
Not really...
Before reducing this final number down further, there are some double digit numbers that DO NOT get reduced further. They are:
a. 13,14,16,19 b. 11,22,33,44,55,66,77,88,99
These numbers have to be left as is. So, today, May 23, 2007 would be left as a "14"
Two more examples:
May 24, 2007 = 5+4+ 24 = 5+4+2+4 = 15 = 6 July 17, 2007 = 7 + 4 + 17 = 7+4+1+7 = 19 = left as 19
Final thought - It would be helpful, though not necessary, to have a notation besides these double digits. If series "a" could have a "KD" next to it and series "b" could have a "MN" next to it,
I believe there is a way to summarize monthly data with the month in rows and the heading across columns. The goal is to have a formula summarize the data with Q1 through Q4 in rows and headings across columns and vice versa. Sample data attached.
In Column A, I have the dates listed as such. Common point is that, they are all 1st day of the month. In Column B, I have Quarters as such.... (Please see below)
Can I put a formula together, to round up how many months left in that particular quarter ? example
My list has dates. I am using this formula to determine the quarter for the date: ="Q"&INT((MONTH(S2)/4)+1).
As well I am using a formula to determine the month for the date: =E2-DAY(E2)+1.
In my book I have some sheets showing totals by month, in other I total up by quarter. I am experiencing a variance between the quarterly total by months and the quarterly total by quarter.
So I thought I'd try this formula to reconcile the two--but no success: =SUMPRODUCT((System=$B5)*(Created>=Lists!M$2)*(Created
this function I manage to select the result in terms of the objectives of the product code. and I need to search for a piece of the product code. The results will be the same in accordance with fragments but not the exact code.
I have thousands of data entries (each in their own row, all in one column) and need to separate the basic info. In the example below, say you want to isolate the Town names by entering a formula that can produce a TRUE or FALSE result. Is there a way to use the FIND function and a pool of terms to isolate cells that contain excess data (i.e.- produce a FALSE result for cells containing any words from a term pool like : "Police, Park, Power, Water, Community, Bakery, Grocery, Field, Garden, Post Office, School" etc... ) ?
Pic of sample spreadsheet with desired result:
Screen Shot 2014-03-18 at 3.14.09 AM.png
example as text:
A2 = Springfield A3 = Springfield Police Department A4 = Springfield Community College A5 = Springfield Park Of Recreation A6 = Des Moines A7 = Des Moines Power & Water Company A8 = Des Moines Sunrise Bakery A9 = Des Moines Post Office A10 = Des Moines Grocery Store A11 = Lancaster A12 = Lancaster Baseball Field A13 = Lancaster Botanical Gardens
1) This exercise would save me considerable time and would almost certainly eliminate the possibility of typing errors in preparing the input data file for another application.
2) My program requires each input multivariant equation to be specified as follows and in the same order:
--Number of Independent Variables, N (max 10) --Number or Terms, M (max 20) --Term 1: power of var 1 : power of var 2 : .............. : power of var N : coeff --Term 2: power of var 1 : power of var 2 : .............. : power of var N : coeff ........................ --Term M: power of var 1 : power of var 2 : .............. : power of var N : coeff 3) It would be extremely helpful if I could type the equation (of up to 10 variables and up to 20 terms) in a cell on a w/s and automatically get the above particulars extracted and nicely tabulated in a column.
4) Simplified Example: (3 var & 4 terms).........................
I need a formula that can automatically populate yearly quarters in separate columns based on a number of years. So if I put in 5 years - excel would populate 20 columns with Quarter 1 - Quarter 2 and so on. If I then change it to 3 years it would only populate 12 columns.
how to calculate the time between two dates in years and quarters (represented in .25 increments). I would round down on the .25 increments (3 months) if not fully surpassed.
See attachment on what it should look like. C2 is my calculated column.
