Below I have a table with dates. The actual dates will be added in over time. I want to display the amount of days in the column btwn planned and actual but since it is subtracting a date from a blank cell, the #VALUE function is displayed. What function can I use to tell excel to keep the cell blank until a value other than #VALUE appears.
I have a document that I downloaded some data from the Internet "Kronos" and within this data there is a column for date. This column is formatted as Date (01/08/13), but it only displays the date as Sun 1/01. It's missing the year, which it should be 01/01/12. I have tried to use TEXT function to convert it to month name and day name using TEXT(N4, "mmm") and Text(N4,"ddd"), but I still getting Sun 1/01. I have also tried to use Text to columns to split this date but, it does not work. Is there any other way I can convert this date into m/d/yy?
I need to be able to take someones name which is in a single cell in the format "First Middle Last" (could be more than one middle name) and then split it up so that it fits the following format:
Last name (must be 50 characters) Middle name (must be 15 characters) First Name (must be 15 characters)
I'm unsure on the right combination of operators to use to variably concatenate strings in this way. I'm confident I can figure out how to add the white spaces after a name to make it fit the character requirement but I don't know how to split the initial name up into it's 3 parts. Names longer than the character requirement will just be truncated.
I am looping through and copying screens of mainframe data and appending them to the clipboard. Once done, I start a Notepad and paste it for future massaging. Now I need to perform a check on each screen which requires copying something from the screen, assigning to a variable, and checking it. This overwrites what I've already stored on the clipboard. Is there a way to store what I've got on the clipboard, then use the clipboard for something else, and then reload the clipboard?
The data is arranged as such, there are a list of call types and in another column, there are a list of call lengths. Currently I have a COUNTIF which searches the call type column for certain call types ie BA is off peak but this only tells me how many off peak calls they have made, I need to make it tell me how many minutes of off peak calls there are. IE this is an example Call Types Call Lengths (minutes) BA 1 BA 1 BA 3 BA 2 at the moment my formula would only count this as 4 but I need it to calculate it as 7. This is a very big data set containing around 900 entries per sheet.
I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.
Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.
With my limited knowledge I am having trouble constructing formula to break the following info that I receive frequently from my credit card company into 3 columns - Date, Details, Value
I am trying to let the user call my subroutine in the following 5 ways but am lost using string manipulation -
Call FindReplaceAll(C, 123, 1.23) In Col C replace 123 with 1.23 Call FindReplaceAll(2, 123, 1.23) In Row 2 replace 123 with 1.23
Call FindReplaceAll(C:E, 123, 1.23) In Col C-E replace 123 with 1.23 Call FindReplaceAll(2:4, 123, 1.23) In Rows 2-4 replace 123 with 1.23
Call FindReplaceAll(C9:E19, 123, 1.23) In region C9-E9 replace 123 with 1.23
Here is what I am trying to do in psudo Sub FindReplaceAll(sType As String, sFind As String, sReplace As String) If sType does not have a colon in it then If sType = A-IV or sType = a-iv only then Columns(sType &:& sType).Select If sType = 1-65000 then Rows(sType &:& sType).Select Else
I have test.txt file. The data in test.txt file so messy (there are headers and some data lines start with letter "A" and need to ignore). There's no <TAB> in data file, all are single space and <enter>. I can do nothing about data files.
Task: Open test.txt file SUM all product quantity, put result in xls file in A1 SUM all Total price in the txt file, put in xls file (same file) in B1 Close test.txt file
Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.
I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.
What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.
The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.
My pivot table is not auto summing each column properly, and I can't manually enter a formula to do so. I tried adjusting the settings of the rows and columns through the pivot table wizard, but no luck. Is there a way around this limitation?
i have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???
Sub nSheet() Dim nm, i As Long nm = Worksheets(Worksheets.Count).name i = 1 Sheets.Add Do While WorksheetExists(Left(nm, Len(nm) - 1) & i) i = i + 1 Loop ActiveSheet.name = Left(nm, Len(nm) - 1) & i End Sub
I have a program that updates values quarterly when run. The spreadsheet that it works with also contains a couple of pie charts that correspond to the updated data. the charts only have one series with category values. I recorded a macro to see the code excel uses for creating a new chart and tried to modify the statements to my needs but have been running into runtime error 1004 ("Method ' Cells' of Object '_Global' failed"). The code is as follows I don't have any trouble until the last two statements.
Sub Chart_Updater() Sheets(Chart1var).Select With ActiveChart .HasTitle = True If Chart1var = "COLI VUL 1 Fund Chart" Then .ChartTitle.Text = "COLI VUL 1 Allocation by Fund Provider " _ & sday & "-" & sday & "-" & Lyear ElseIf Chart1var = "COLI VUL 2 Fund Chart" Then .ChartTitle.Text = "COLI VUL 2 Allocation by Fund Provider " _ & sday & "-" & sday & "-" & Lyear ElseIf Chart1var = "COLI VUL 7 Fund Chart" Then .ChartTitle.Text = "COLI VUL 7 Allocation by Fund Provider " _
The variables fundtr1, fundtr2, and emptycolvar are public, and are all returning values, so there is no problem there. Also I can't use ranges like "A1:F7" because the range needs to be variable.
I've created a humongous spreadsheet in Excel- 26 million cells and a file size of about 500mb. The result of these calculations is a pattern derived by conditional formatting. I'd like to get the pattern into Photoshop- It would actually simplify image manipulation in Photoshop to lose the cell data, and the underlying formulas are of no use.
The file seems to be too big to save into a PDF file. Is there another way to get the Image into Photoshop?. The other alternative is to reduce the file size by stripping out unnecessary cell data and formulas without losing the pattern.
Data manipulation question here: how to dynamically filter and sort a multi-column data set? My end goal is to be able to (1) quickly collapse all columns into one single column, (2) remove all duplicates, and (3) sort the information in ascending order. A reference sheet is attached in case it's useful.
I basically want the end result like Column Q shows. If there is a value (doesn't have to be 1), display the date on top. What I did to get this work was for each cell I put a IF statement. Then I summed it up and made it Text formatted with mm/dd/yy.
I need B1 to show 1 year from A1, unless it has already passed, in which case I will need additional years added until the date being displayed is either today or in the future.
For example, if A1 is 1/1/2010, B1 should be 1/1/2015
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.
Im having a little issue with the way the dates are layed out.
I have a condition format =AND(RC5="No",TODAY()<RC4+10)
And withen the cell's the date is located, If the cell is writen Month/Day/Year everythink works.
Though i have always written dates like Day/Month/Year. ive tryed to do go to the number format thing and change it, though that just seams to change the end display not the way excel handles the date. it there a system level change that can be made of change somethink.
I am using a worksheet change event to display the date and time that certain cells are manually changed. However, I would also like this to happen when a cell displaying a value based on a formula changes.
