I have a Cell and the Formula for my Cell is the Following:
Code:
="Week Ending " & 'A01'!D248
The A01 Worksheet displays the value as 4/21/2012 . However, the first worksheet displays the value as
Code:
Week Ending 41020
rather than
Code:
Week Ending 4/21/2012
What can I do to display the cell the way that I want to? I've already tried formatting the cell directly as Date to no avail.
I need to display all text after first comma in a cell in another cell. For example
Text in A1 = 1.1, 1.2, 1.3. I need to display in B1 all the text after the first comma which would be = 1.2, 1.3
Each time my spreadsheet is modified and saved, I would like the date of this action to appear in a cell. I think this may involve VBA but I'm not sure.
Let's just say, the cell is L54. I am using Excel 2007.
My head hurts from working on this all day. I have a large spreadsheet and in the top left corner I need to show the percent of vehicles that are not working out of our whole pool. I have a function that detects the color of the cell (Red is broke) and counts the total of red cells and then divides it by the total giving the percent and it is in G118 for Jan 1/2007, H118 for Jan 2/2007 etc. I made a function that will count Julian days from Jan 1, so for today I get 114. I know I need to display G + 114 columns but have no idea how to get that column name from this and always display the current one in the corner.
View 9 Replies View RelatedI am using the following math formula in cell C12:
=IF(B12="","",IF(B12<2,-1,INT(B12/5)))
What I want to do is if cell B3 equals a text value of the word "All" then cell B12 would display nothing. If B3 equals any other value then the math formula above be used.
How can display the last modified date and time of my excel file in a particular cell - Say in cell E1.
This is the same value that we get when we Right Click excel file->Properties->General and look at the label "Modified".
I'm having to re-learn formula commands in order to continue to use my spreadsheets (I had tried to get the Quattro Pro converter to work in Excel 2003 with no success). Actually, I would have stayed with QP since I find it much more user friendly, but with Excel being the standard spreadsheet that most people use, I figured I better get used to it. Anyway, I'm currently trying to find out how I might get the same text that shows up in varying cells in an otherwise blank column (other cells contain formulas, but no text) to also show up in another fixed cell (which can be where I put this formula).
View 2 Replies View RelatedI am trying to display watermark text in a cell based on the value of another cell. For example:
If in Column B, cell B2 states "Mileage", Column C, cell C2 needs to say "Please enter Start and Destination...". I want column C (cell C2) to be my input cell as well hence the reason i'm avoiding the use of an IF formula.
I'm open to a VBA solution if this works? Tried conditional formatting however it will only display colours, not text?
Has anyone ever seen a problem like this? The Legend text is not displaying what is in the cell reference and I cannot figure out why.
View 9 Replies View RelatedI'm trying to get excel to display a quote, plus a cell's text/value... the problem I am having is that the format is a date, and it ends up showing something like 37896 instead of 11/25/2008 (those are examples, not actuals). Here's the
View 3 Replies View RelatedI have three cells in A2:C2 which require user to input some data.
What I want to achieve is to combine the data from A2:C2 in D2.
C2 is a field which user will input the date. He might key in 21/08/06 or
21/08/06, 30/08/06
I have tried using below formulas in D2 but without success.
=A2&" " &B2&" "&(C2)
=A3&" " &B3&" "&DAY(C3)&"/"&MONTH(C3)&"/"&YEAR(C3) (doesn't work if there are 2 dates.
I have attached a file which shows 3 scenarios if user input 1 date and 2 dates.
I want to enter the word 'Closed' in cell B1 when the due date in cell A1 is exceeded by 272 days. I have tried using conditional formatting and excel accepts the formula but nothing seems to happen. Here is what I have tried;
B1=IF(A1<=NOW()-272,"Closed")
I have the the following spreadsheet: [URL] My formula in Columns A to E is the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE))
I would like to do the following in Column A: If the date in Column C is greater than today, then Column A must also say "Branch Not Open". I have tried the following:
=IF(ISNA(VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),"Branch Not Open",VLOOKUP(Query1_2[@Branch],Master!A:J,10,FALSE)),IF(C:C<=TODAY,"Branch Not Open, IF(AND(C:C=>TODAY,VLOOKUP(Query1_2[@Branch],Master!A:I,9,FALSE)))
But Excel does not like the formula at all.
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
I'm trying to write a macro similar to the one found here: here:http://www.techonthenet.com/excel/macros/checkbox.php.
Rather then use a textbox, I would like the cell to display the date when text is inserted in the cell to the left.
ie. I insert text (the letter 'a') in cell E11, and the date appears in cell F11.
I would also like the macro to do this for a range of cells ie. for E11 to F21, then from G11 to H21, then from I11 to J21, all the way to column IV.
my worksheet have a a lot of date cell (dd-mm-yyyy), if I changed the format to yyyydd. it will display "200612". However, the cell format still showing orginal date. Can you change it to text format (200612) in the cell when I type 200612?
01-12-2006....
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
look at the attached. In the estimate tab look at the box highlighted in yellow. Then look at the cells in pink (row 70). F70 is selecting the lowest maintenance value from the yellow box but I want C70 to display the hours associated to that value. The correct hours will need to appear according to what value is displayed. (this sounds confusing but look at the formula in F70 and you will hopefully see what im trying to achieve).
View 2 Replies View RelatedRefer to the attached worksheet. On entering a value into cell C7, I'd like the value of the adjacent column D7 to be displayed in F6. Then when a value is entered in manually into C8, it will then update and put the value of D8 into F6. The idea is an individual will enter a value each week, updating that value. I intend to hide column D.
View 2 Replies View RelatedI want to have a function that finds the largest number in a selection range. However, i want it to display what's in the cell beside it! Example:
Say the largest value is in B6 well I was wondering how you would go about displaying the value in C6.
I have the following columns that return cover period dates.
Start of Cover PeriodEnd of Cover Period01-Nov-0631-Jan-0701-Jul-0831-May-0901-Dec-0930-Jun-1001-Jan-1131-Dec-10
I however need a macro that will delete the cell contents if the "Start of Cover Period" (column AK) date is > than the "End of cover period" (column AL) date.
Thus it must compare ak2 with al2 and if ak2>al2 then clear both cells. if AK2
I have a spreadsheet and certain cells are coloured to represent certain
criterier, I would like to display all of one coloured cells to show status
of that colour, say I want all the red cells locations to be displayed so
that i can check the status.
TWO more days to turkey day. 3 more to BF --- yaaaaak! Anyway,
Iam trying to display only 3 workdays in each of three cells. Days displayed depend on today's date.
I am usinf the following but it displays SAT and SUN.
Q3 = today's date
=UPPER(TEXT(Q3+2,"DDDD")) displays THURSDAY
=UPPER(TEXT(Q3+3,"DDDD")) displays FRIDAY
=UPPER(TEXT(Q3+4,"DDDD")) displays SATURDAY -- would like it to display MONDAY
Q3 changes to reflect today's date
therefore this would display:
=UPPER(TEXT(Q3+2,"DDDD")) displays THURSDAY
=UPPER(TEXT(Q3+3,"DDDD")) displays FRIDAY
=UPPER(TEXT(Q3+4,"DDDD")) displays MONDAY
In other words I want to display only the week days in these 3 cells.
I tried:
=TEXT(WEEKDAY(INDIRECT("Q3")),"dddd")
but "Q3 + 2" will not work
the end result desired:
MONDAY
TUESDAY
WEDNESDAY
TUESDAY
WEDNESDAY
THURSDAY
[Code] ......
Am trying to create a simple spreadsheet with some multiplication functions on it. Using this example:
Cell A1 = 5 (multiplicand) x
Cell A2 = 10 (multiplier) =
Cell A3 = 50 (product)
All is well providing I have a number value in cells A1 and A2. When I don't, I would like them to remain blank, as well as A3, the product. The problem I face is when I remove the values in either A1 or A2, what's displayed in A3 turns to the word - #VALUE!. Which also shows up on the printed page.
My question is, like removing the checkmark for "zero values" under tools and options to prevent the "0"'s from being displayed, is there a way of preventing the word "#VALUE!" from being displayed when the values for the multiplicand and/or multiplier have been removed?
Is there a command or macro to display the name of a sheet in a cell?
I know how to do it in a header or footer, but not in a cell.
I need to copy the name that appears at the bottom of a spreadsheet in a cell at the top -- no need to reference the name of a different sheet, just the same one the cell is on.
I have a row of raw quality control data, but some are not in control and should not be used in the calculation of statistical data. There are not a lot of them, so I can manually select these (I use a red fill color for the cell to signify bad data), but I would like to be able to keep these data displayed but not have them included in the calculations.
View 3 Replies View RelatedI need to change the date format in a cell which also contains text.
The full formula is as follows:-
="Report period:
"&TEXT(Control!B9,"dd mmmm yyyy")&" - "&TEXT(Control!B10,"dd mmmm yyyy")
This gives me the following:-
Report period:
01 October 2012 - 30 September 2013
What I would like to have is the following:-
Report period:
1st October 2012 - 30th september 2013
It's only a minor change but I think it looks better. Without a hideously complicated formula, if this is possible using one of the standard cell formats?
I am trying to concatinate a cell to show both text and date (in format mmmm dd yyyy) using a vlookup from another worksheet.
Below is the coding I have in the cell:
="High Importance Request - effective " & VLOOKUP(1,'Fuel Surcharge Key Dates'!$A$6:$E$65536,2,FALSE)
The result I get in the cell from the formula is: I still get it to come out to 39327 instead of June 4 2007
The vlookup in the formula above retrieves a date that has a cell custom formatted to mmmm d, yyyy ... as this cell is not a date that is entered by a user but is further retrieved by another vlookup of dates. Even if I place the VLOOKUP in cell A3, reformat that cell to be a date and change the coding in the formula to:
="High Importance Request - effective " & A3. I still get it to come out to 39327 instead of June 4 2007
I am pulling reports out of a non-windows based program. This program spits out the data directly into an Excel spreadsheet, with but a click of a button. I imagine that the designers of this program assumed that this would be useful, but it's actually proving to be quite irritating.
The Date format they have chosen for their program is NOT the date format that Excel uses, but it resembles it enough to cause serious problems. The original program tracks dates in a "dd/mm/yy" format, rather then Excel's "mm/dd/yy". To my VAST personal irritation Excel does NOT convert this into Text (which could be
manipulated by snipping it into the correct format) but instead converts it into it's own Date format, leaving me with a useless and incorrect serial number as the only data.
I cannot interact with the exporting process at all, and as the program exports DIRECTLY into Excel I cannot manipulate the data prior to import (which would be one relatively easy way of avoiding this problem). I am left trying to figure out a way to reconstruct an actual date out of this mess. Is there any way to convert a date serial number BACK into text?
I have column h with address number.
I have column k with street name.
In column iv i have an x an number or it is empty.
What I want to do is press a button and it starts at iv10 read that cell if empty then read iv11 etc.
When it reads x in the cell then read next witch will be a number.
Example:
iv10
iv11
iv12
iv20 x
iv21 21
So I would get columns a - ae and rows 10 - 21 displayed and wait till i press button again then when i press button it starts
Example
iv22
iv23
iv89 x
iv90 68
then it would display a-ae 22 - 90