I'm trying to have a fixed date entered into column B when data is entered into column D. Here's the formula in column B:
=IF(D3="", IF(B3=";", FixedDate(),B3))
When I enter data into cell D, I get the following result in B:
0-Jan-00
Taking this a step further, even if I do a "control + ;" I still get a value of
0-Jan-00 in the cell but the formula bar shows me "=1/9/2007'. ??
I've gone through all of the options but cannot fix this error. I have two spreadsheets using this formula. The first one worked fine last year but now I have this error in it. I also started to create a new sheet and still have the same error.
I’m trying to resolve this issue and would appreciate some help.
Scenario I have a tracking sheet that tracks development of work to be completed by individuals. I have a due date column that shows when the work should be completed. Once the work has been completed, the user enters his completion date.
I have a formula that provides an overdue warning (Completion date – today’s date) and some conditional formatting.
Problem When I copy the formula through all the cells in the column I get a number (example 39504) and this changes everyday.
How can I eliminate this being shown as it throws out my average development day calculation?
I have formatted cells to date but when I enter in a date of 020413 (Feb 04, 2013) my reslut shows as 11/20/55. I have a coworker who is having the same problem (but hers displays even a different date) so was wondering if there is a setting in excel that I don't know about.
In the attached sheet, one macro is developed which is not working properly. My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)
If incomplete date is there, then hyphen should place the gap i.e.
For date like APR-2014------> Output should be 2014/04/-- For date like 1998 -----> Output should be 1998/--/--
The below macro works only for row number 11, 15 and 16.
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size.
But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly - since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
I have a rank formula that is seemingly erroring out on certain cells - indiscriminately. I have tried and tried to figure this out for myself but this is beyond my knowledge.
Only on some cells is the rank not working and returning a "-" (the error result).
I want the rank to use absolute numbers, so that it will rank according to variance or percentage (regardless of +/-). I am also using another cell (R1) to tell the rank what column to use.
As you can see there are a few entries, both + & -, that are not being included in the rank - for reasons beyond me.
I have 6 categories that need to be ranked 1-6 in order of highest number of occurances. My ranking formula is showing 1-7, missing number 3. I have attached a sample worksheet further showing what I am trying to explain. I need cell A3 to show a ranking of "3". Currently it shows "4". What am I doing wrong??
I have a file that has start date column. I'd like to show the account's expiration date on another cell but it's going to be dependent on another column.
Say Column A - customers name Column B - start date Column C - rate Column D - End date Column E - Follow up Date
Rate column will have the ff inputs : rate 1 = .09 rate 2 = .11
If rate is .09, then end date will be 2 years from the start date If rate is .11, then end date will be 3 years from the start date
There are several things I need to happen : 1. Show the customer's individual end dates in Column D 2. I'd like to show in column E the follow up date which is 90 days before the end date
I have a spreadsheet that has two columns. Column A is a date format and column B has the number of days between both dates over 2 years analysis is =A8-A7.
However when i come to build a chart its all wrong as the dates are following a weekely format, can i adjust this to pick up only the dates that are showing on the cells in column A? also is ther ea way which teh #VALUE! doe snot show?
Im having trouble with a combo box with a range of dates as the drop down list.
The Named Range it is connected to is as below
01-01-10 02-01-10 03-01-10 Etc
But when i run the user form the Combobox drop down box show as below
40179 40180 40181 Etc.
When i Select one the combo box does then Show the list in date format
How can i make the dropdown list show in date format also below is the code i currently have for the combobox
Private Sub ComboBox4_DropButt*******() ComboBox4.List = Application.WorksheetFunction.Transpose(ThisWorkbook.Names("Date").RefersToRange) ComboBox4.Value = Format(ComboBox4.Value, "dd/mm/yy") End Sub
I'm trying to concatenate quite a few cells together. One of the items being included in the concatenation is a date, however when I do this the output is the numerical version of the date that Excel uses to mathematically calculate with. My question is how do I do a cell concatenation with the desired output showing the date, not the numerical equivalent used by Excel? Book1.xls attached is what I'm talking about.
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
I have a worksheet with a range of dates in column E. What I would like is a formula that states If any of those dates is less than or equal to 10 (days) from today and column H text is not "Yes" then show text "Email outstanding Actions" else show nothing?
I have an infinite list of consecutives dates on a row like the one below. I want that everytime I open my file, the first column shown is the one containing today´s date. If today´s date is 16.Feb, then the first column must be the one containing 16.Feb and so on.
I can't get the zero's to show up in the part number formula in the wooksheet. Also how do you get the zero's to stay when you type numbers such as (03,04,ect.) the zero always disapears. I need it to stay as a number and not text.
why formula's I type in to a particular worksheet show all the time(this is a workbook containing numerous macro's). I have checked under 'Options' that the show formula's box is not checked
I created a formula in a cell and it worked fine but then I created the same formula in the cell just below it and I changed some of the numbers and when I hit the enter button the formula showed up in the cell and not the final result which would be the answer to the formula. Does anyone know why it would do this? I have an example below....
I have a spreadsheet (attached) that has a formula, which calculates the number of days between two dates if another field contains data. My problem is that when I chart the data, it isn't showing up in the chart. Cell G1 (Tab 1) contains a 1 because the document was returned 1 day late. This isn't showing up on the Chart (Tab 2).
I have the formula (found in cell "C2") on the Report sheet. I need to perform a function, but I cannot get it to work on the sheet I need to pull information from. The sheet RecapWk12 has a small section pasted (with some cells edited for obvious reasons) from the actual workbook. I can get the formula in Report cell (A10) to work on pulling information from sheet2. You can see I am getting (#REF!) in cell C2.
I have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.
The same formula could be another place in my worksheet and work fine.
I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.
I was given this Formula to use in a cell to calculate the total hours from 2 different cells, from inputting date and time from and to dates..as an example from 4/1/14 0:00 thru 4/1/14 12:00.....The problem I am encountering is that when the spreadsheet is 1st opened "+1" is visible in the cell where this formula is. I am asking how the formula can be altered to have the "+1" not visible in the cell before any date and time is entered.
I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version) ******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1= ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as =SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))), --(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9)))) Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win............. .
