I'm using the following:
B23=IF(A23="","",DATEDIF(A23,I3,"y"))
Where:
A23 = a date of installation
I3 = TODAY()
B23 = a number of years
It currently calculates correctly if the number of years correctly if it's older than 1 year. If under one year, it yeilds 0. I would like B23 to show 1 if the current formula yeilds 0.
I want it to yeild a 1 if the current calculation is 0.
Windows 7 Ultimate / Excel 2010
I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.
Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))
Example:
If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.
Windows 7 Ultimate / Excel 2010
I am using 2010 to write some code for a spreadsheet that will be used in 2007, so I know that RepeatLabels is not available.
My question is if you Group a date field by Months and Years and the data spans across 2 or more different years, is there a way to hide months in a specific year? I ask because the Date field has only 12 months in it and if I hide Jan (for example) it hides Jan in both years. (attached pic of filter for Date field below.)
Is there some other way to format it so it can discern between years or do I need to add yet another column to my original data?
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.
There are two columns in an excel sheet, one is date of birth and other is date of reteirment
looks somewhat like this:
D_O_B D_O_R
5-Mar-53 31-Mar-11
30-Jun-57 30-Jun-15
20-Jun-51 30-Jun-09
2-Feb-55 28-Feb-13
2-Jul-51
13-Oct-55
1-Sep-51
7-Jul-54
14-Mar-53
3-Aug-50 3 0-Sep-13
Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column.
D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)
EXCEL 2010. I have a column of negative and positive times (eg. -00:52, -03:07, 06:02) in custom hh:mm format, and would like a new column rounding these times to the nearest negative or positive hour (with just the whole hours displayed), so I need a formula that would give me (for this example) -1, -3, 6, etc.
View 9 Replies View RelatedI have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
I have a number, eg, 1234567 and rounded to prepare the shrink of the size of the number like
= Round(a1, -3). I have, say now, 1234000.
I need to strip "000" from the number and place 1234 to a cell.
I need to put a number as a text. It is 14 digits long (35071245130000). If I change it from a number to a text, it re-formats to a scientific number (3.50712E+6).
I can not use the ' in front of the number because it makes all of my vlookups mess up.
The reason I need this changed is because I have a several databases/excel spreadsheets that are being linked through Access. The number is stored as text in the other databases. Because of this, the spreadsheet with the number is not recognizing the number version and the text version as the same.
How to make excel show this full number when formatted as text?
I have Excel 2010. I do not remember having this problem with other versions at my previous employer.
I have two dates, one is a start date the other is todays date, I want to subtract the start date from todays date and show the number of years. But a small twist is I only want to take the years away from each other, ignore day/month. Start 01/05/2000 todays date 01/10/2008 years = 8. Start 10/10/2000 todays date 01/10/2008 years = 7, want it to still show 8 years and ignore the start day/month.
View 2 Replies View RelatedI have a section of code that build a single sheet workbook that incudes names and phone numbers to upload in to a web site.
The numbers are in internation format eg 4412345678987
The code works fine but when it saves as a CSV Delimited it round the numbers up so they end up 4412346000000.
I thought this was really easy and I swear that I did this before but I can't remember it at all. What I'm trying to do is take 2 user inputted dates and subtract them to get the total days. After that I divide it by 7 to get the total weeks. and ususally I will get a decimal. However I need to round this up to the next whole number.
View 9 Replies View RelatedI have data arranged in a worksheet (see attachment) that has hours of work broken down by day. What I need is a formula that will find the number of times a record occurred in Column F that is greater than or equal to 12 hours each day. So for March 1st there would be 9 times. I can do that now with no problem using "=COUNTIF(F4:F14,">=12")" However, the real thing that I need is how many days of each month were there only 1 count (of 12 hours or more). So it needs to look at the range of data that goes from 3/1/13 to 3/31/13 and find the total number of days that had 1 count (of 12 hours or more) each day and return the number of days it found.
View 6 Replies View RelatedI am trying to format a cell (in Excel 2010) as a phone number. The problemis that the data comes over from our download as text. Is there an easier way then using the 'SUBSTITUTE' function to change format to a true phone number (so that we can mail merge into WORD)? Since it is extracted out as a text, using the 'phone number' format (under special) will not work. Using the 'SUBSTITUTE' formula to remove the parenthesis', the dash and the space work but is cumbersome.
Below is an example of the text format that is being extracted: (706) 378-7585
The setup of my worksheet looks like this:
Excel 2010ABCDEFG1Tch grpTchr Split27B-MA5AWAWAWAWAWAW37B-MA4CB1,JDCB1,JDJD,CB1MMF,JDMMF,JDMMF,JD47B-MA3SXGSXGSZBSZBSZBSZBTchGrps
A great chance to use the HTML Maker for the first time!
What I would like to do here is show how many of each value there is in the range.
So for 7B-MA5, it would say 6. For 7B-MA4, it would be 2 / 1 / 3, since there are 3 unique values and their counts respectively. Finally for 7B-MA3 it would say 3 / 3
A formula option would be great but I'll also take a macro option since it is a macro that generates these lists in the first place, so I could just add the new code to the end of it.
I need a method or function that can count the TOTAL number of pages to that will be printed. I don't need anything with HPageBreaks or Get.document(50, ""sheet name"")") for these don't seem to work on my worksheet.
All they seem to do is give me a number lower then the number of pages that will be printed and they are dependent on where the active cell is.
Here is my code that does not work.
Code:
Agreement_Page_Count = ExecuteExcel4Macro("Get.document(50,""Signature Page"")")
and
Code:
Function HowManyPagesBreaks(sSheet As Worksheet)
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer
With sSheet
[Code] ........
Both codes return 2 when there are 3 pages that can be printed. Unless I pause the code select the cell at the bottom of my sheet (it ignores it if I use use a macro to select the bottom cell) and then continue my code.
I've tried it with and without the entirerow.hidden.
Is there a way of bypassing everything and using a marco to open up page preview and getting the total number of page to be printed? I'm using excel 2010
1) What's the maximum limit of IF statements in a formula for MS Excel 2010?
2) I have 8 nested IF statements, but I am having trouble with the False part of the argument. I was able to get the False part to work for the 4th argument (because I simply put a comma and closed it's respective colored parenthesis) but the following 3 I can't seem to get and the following message pops when I hit enter "The formula you typed contains an error."
For the False part of the remaining 3 nested statement which is at the end of the formula I just type:
IF($E$2="Q3"........)))IF($E$2="Q2".....)))IF($E$2="Q1")))
And close it's respective colored IF statement argument. I tried putting a comma at the end of each False part for the arguments but it pops with the message "You've entered too many arguments for this function."
I have a date 07/28/2027 and need Excel to calculate a date 65 years in the future taking into account leap years.
View 9 Replies View RelatedI have a list of people with birthdays that needs to be checked against TODAY to determine how old people are. If I subtract the two fields from one another, I get number of days. But is there a way to convert into years? In my attached example, I'd like column C to display 3 (age for person).
View 3 Replies View RelatedI need to round up the answer of 1.72 to 1.73 for the formula listed. The formula in that cell reads as follows: =(22/12.762)
View 2 Replies View RelatedI have a column of numbers that represent sales prices.
If the price ends in anything between .x0 - .x4 I want the replacement number to be .x4 and if anything between .x5 - .x9 I want the replacement number to be .x9.
For example, the sales price is 1.93. The "rounded" number should be 1.94.
More examples:
3.76 = 3.79
3.13 = 3.14
2.50 = 2.54
I have an overtime spreadsheet where I calculate how mush I'm paying on vouchers, as they only come in lots to £5.00 I want to round the figure to the nearset £5.00, doesn't really matter if I overpay as it will even out next time, what's a basic forumula to work this out?
View 6 Replies View Relatedi have done in Word VBA successfully (basically, change the Style for a certain number of lines):
VB:
Dim myRange As Range
Set myRange = ActiveDocument.Range(ActiveDocument.Range.Start, ActiveDocument.Range.Start)
[Code].....
I'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.
My data is similar to the excel I've attached
Count completes.xlsx
I'm using Excel 2010 too.
I have a 2010 version of MS Excel. I have roughly 10000 cells that I need to separate into two columns from one cell.
Here is an example of one cell "John Smith 888-8888".
Excel 2010 O/S XP Macro for serial number input all serial numbers have JTC0. Any VBA code to place in macro to have a cursor appear after the four characters in the macro below?
Example: JTC0| < cursor ready for input
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+s
'
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "JTC0"
End Sub
I am using excel 2007 or 2010.
I need to divide the numbers on sheet 1 column D. This column contains numbers and blank cells so I need to divide by the actual number of cells that contain only numbers. I will be referencing the divided numbers on sheet 2 in a monthly summary format.
Excel 2010
Sheet 1 contains range B12:B353 with names of people. Sheet 2 range B12:B353 contains a formula to extract the names from Sheet 1 if range D12:D353 contains a certain value:
=IF(Sheet1!D12:D354="A",(Sheet1!B12:B353)," ")
At the bottom of each column in Sheet 2 I am trying to get a total count of populated cells (a running count of names actually visible). I have inserted formula:
=COUNTA(B12:B353)
Which should count the number of cells with text in them, right? But I believe it's somehow counting the formula as text because I inserted the value of "A" in Sheet 1 Column D for 3 names. It returned a number of 331 at the bottom of my column in Sheet 2. First off, a return of 331 doesn't make sense in any way (still scratching my head at this return. It should have returned a value of 3.
I am trying to create a formula for a work report and I am afraid I am a bit of a novice with the more complex formulas. Here is what I am trying to do: Sheet 1 has Sales order numbers that duplicate based on the sales lines. Sheet 2 has single lines of sales order numbers and their assigned PO number. I would like to have Sheet 3 where it combines all data and duplicates and fills in the PO number to match all of the sales order numbers that repeat on the other sheet. I hope this makes sense. I can't seem to figure out if I need a VLookup or If or Match formula (or all of the above).
View 1 Replies View Related