I have a number in A1, another number in B1, a Date in C1. In D1, I am referencing all 3 of the other cells: =CellA1 & "-" & CellB1 & "----" & CellC1. Trouble is the date is coming through as a number. Is there a way that I can get the date to show as displayed as opposed to the value of the cell?
I have written some code which takes data from multiple spreasheets and formats it in a specif way. However one problem I have is getting the right data format. As you will see from the input files the date and time cells are separated into two and the macro is supposed to aggregate these together in one cell with a space in between - but I cannot manage to perfect this !!! The output is in a number format instead. I have attached the input files. The code written so far is as follows - works perfectly apart from date format that comes is in the output file once you run the code.
Dim Obj1 As Object Dim Obj2 As Object Dim UserList() Sub RunMe() Dim Str1 As String Dim myPath As String Dim i As Integer Set Obj1 = CreateObject("excel.application") myPath = "C:Test" 'please note you should a "" at the end. Str1 = Dir(myPath & "*.xls") Set Obj2 = CreateObject("excel.application") Do Obj1.Application.DisplayAlerts = False Obj1.Workbooks.Open (myPath & Str1) 'For i = 1 To UBound(userslist).............
I have two cells. In cell A1 there is a text "(fi) = " and in A2 a number, for example 30. (fi) means a symbol for diameter (letter "f", using Symbol font). Is it possible to joint those two cells in third cell using formula "=A1&A2", maintaining original fonts? What i get is "f = 30", not "(fi) = 30".
I'm usually able to find my answer but I couldn't find anything to match what I need. Anyway... this is either really simple or impossible to do. I have two cells which have times in each. Say A1 has 5:00 PM and A2 has 10:00 PM. I'm just trying to make it so one cell will say:
From 5:00 PM to 10:00 PM... So I tried this ="From " & A1 & " to " & A2
But as I'm sure you already know that yields this result:
I am using this formula to combine Excel Dates & Times which is in seperate cells. day Month Year Time
=B13&"/"&C13&"/"&D13&F13 and trying to convert to Numerical Time for further calculations. I have tried many Format variations but nothing seems to work.
It will show the correct Numeric Time if the Formula: =NOW() is used in Cell K12 But it won't show the same from the imported data that is Text format in Cell I 12
The sample W. Book shows a better explanation of the problem. The end result is to show how much time has lapsed in Cell M 14 by converting Time to Numerical and simply do the sums. Works otherwise but not in this instance. The reason to trigger various macros according to pre-set Time Limits Etc.
I used to get data from a database (CorVu & MIMS) in this format "0122458/001". Due to changes in those Databases I now get the data as 2 columns " 0122458" and "1" .What I need to do is somehow get this back to the old format including the leading zeros.
I'm concatenating data in 5 cells (Author, Year, Title, City, Publisher), one of which is in italics (Title). When using concatenate (or &) the formatting is removed, and I need to be able to retain that formatting.
Example: Col B Aaron, M. (ed) 1999 The Body's Perilous Pleasures. Endinburgh Edinburgh University Press
I have a textbox that displays text from another cell in the workbook. The other text has some sections in bold, but when it displays in the textbox it doesn't show the bold text - The code I'm using at the moment is below.
Private Sub UserForm_Initialize() Dim R As Long ComboBox2.List = Array("QuestionSet1", "QuestionSet2")
My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.
I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.
I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?
I'm using Excel 2003 on Windows 2000 Professional.
I use pivot tables extensively in my job. I have several spreadsheets that feed from acess database tables to build pivot tables. Besides the fields imported from Acess, I also create calculated fields. The problem is, for both the calculated fields and the imported fields, the number formatting does not stick. I know how to go to field settings, then to number, and change the customized format. The problem is, once I take that field out of the pivot table and bring another one with a different format (say, bring a field with currency format and take away one field with a percentage format) and then bring the original field back to the privot table, the number format is gone.
This is really annoying since I work with dozens of fields, and it takes time to change the number settings every time I change fields on the pivot table, and the number of decimal places, etc. I stress that I know how to change the number formatting on fields, it's just that it excel does not remember it afterwards.
I thought that maybe the formulas in excel might allow one to include the formating, but I have not been able to find if this is possible, and if so, how to do it. So, for example, the following formula for a calculated field:
I am importing a data table from Excel into word. All works well except for the first column of my data, which contains numbers formatted as currency. I am using a bunch of loops to transfer the data into word, and for each cell the code looks like this:
Is there a way to tweak the code and preserve the formatting when I am importing numbers into word?
I tried the following code but it gives me type mismatch error:
I have 2 sheets in one workbook; one sheet has a chart with a data table, the other contains the source data for the chart. I want to copy and move the sheet with the chart to a new workbook, but without the source data. I break all links, but then the chart's data table takes the default values and formats in the new workbook after I break the links. For examples, the source data has formats such as currency and no decimal places. However, after copying the sheet, the data table reverts to no dollar sign and decimal places. In addition, the months on the x-axis turn to numbers when the links are broken.
I am using excel 2007 - I need the extra available columns in Excel 2007 for my application.
I have a spreadsheet that has a significant amount of conditional formatting. I want to copy a range from this spreadsheet to another spreadsheet. In the new spreadsheet I want to remove the conditional formatting rules whilst retaining the actual formats those rules generated.
Format painter and "paste special - format" copies any formats as well as the conditional format rules.
Is htere a method within Excel to do this or can someone suggest a workaround?
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
I'm using nested SUBSTITUTE formula to make some changes to the some of the data. I'm also using IFERROR to return the input if it does not find the criteria I have specified. I have lot of variations in my input data.
The formula works fine for all except for date format input.
N90232Y09--->SUBSTITUTE(A1,"N","")-->90232Y09 12335--->12335 12/3/1923---->58936 (I want the date to be retained)
why the date format is changed even if don't specify any changes for it. I expect my IFERROR to just retain the input as it is.
I have a quantity column on an inventory sheet which automatically updates the available quantity based on sales from a sales sheet. But I need to know the date the quantity reached zero.
That date would always be "today," on the day the quantity = 0, but how do I make the date NOT update to "todays date" on the next day, and the next, etc. It needs to stay as the date that 0 qty was reached.
Is there a way to date format a cell so that when I enter only the number date (ie today is October 13 so I'd enter 13) the cell formats to the current month with the date?
So for today I'd enter "13" and the cell would read 13-Oct. Additionally, when the month turns to November that same cell, without me making any changes to it, would change to 13-Nov. For future dates it would always show the current month with the date I entered manually.
I feel like this is a pretty simple question to answer but I cant find an answer to it anywhere or I'm just not skilled enough to understand it. I have two columns D and E that have dates in them. Column D is the purchase date of a computer and column E is how many years months and days its been since it was purchased. I want both columns to be the same color. If the computer is 2 years old or less I want it green, 3-4 yrs old yellow and 4+ red. I have it working for column D but I can't figure out how to apply it to column E.
I thought I read somewhere that it wasn't good practice to format a whole column(s) b/c then you would have empty cells that were formatted. Is there a better way that you guys format your columns without selecting the whole column? I know you can do this in VBA, but I was just wondering if there was a way that I'm unaware of.
Second, is there a formula that can inserted into conditional formatting, that will check a named range against two other cell values. If that ranges date is > AB2 and <AC2 then it will bold that range? I'm trying to figure out how to do things without always relying on VBA if possible.
i've put this previously on here with no results. There is a formula already in there though does not work because what I want to achieve is to have Column A & Column C to Match Column E & Column F, to Return the Value YES or NO. Iíve Highlighted in ROW 947 one that should Say NO yet Says YES.