Convert Imported Dates & Add Periods Based On Conditions
I import date information in text format and need to add to the date. My date info in in column "J" and in Column "K" I would desire to write a formula that allows me to to the following. My data looks like this in text: yyyymmdd
I desire to look in column "E" and I have 3 different variables. "J", "K", "L".
If column "E" has a j add 30 to the years; K add 35 years and L add 38 years.
for Example: 19630923 j = 19930923 k = 19980923 L = 20010923
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Convert Imported Text Dates To True Dates
I have a very large spreadsheet that comes out of SAP and it brings out the majority of cell entries as text ie putting a ' at the begining of any number. the problem that i have is with the cells that have a date in them ie '19/04/07. I have tried varioous methods to remove this and turn the cells into date format but to no avail. Also i do not get the option to convert text to number
View Replies!
View Related
Add To Dates Based On Criteria & Flag Overdue Dates
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View Replies!
View Related
Convert Date Separators From Periods To Slashes
I have spreadsheets with a lot of dates where the date separators used are periods “.” instead of slashes “/”. For example, 12.02.2008 is what shows in a cell. Where I live (Australia) that means 12 Jan 2008. My need is to have Vba code that converts these to dates; e.g. 12/02/2008 or 12-Jan-2008, etc. I can manually select the cell or cells, press Ctrl+H, replace the period characters with slash characters and it works correctly - I get 12/02/2008 – which I can then manipulate as a date. However, if I record a macro of the above actions, when I run the code the result I get in the cell is 2/12/2008 – i.e. 2 Dec 2008. The code recorded is: Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False The only time the code gives the correct result is when the month is greater than 12; e.g. 23.02.2008 is converted to 23 Feb 2008. My computer is set up with regional settings to Australia and date format day/month/year.
View Replies!
View Related
Conditional Formatting More Than 3 Conditions Based On Dates
I want to create a chart than logs the progress of a project at work. Column 1 will have a series of target dates for certain tasks and column 2 will be filled in with the completed dates. I want automatically colour a cell (RC) based on the date given in the cell to the left (RC[-1]) giving a traffic light style warning of an upcoming target date and colour coding the cells when a task is completed on time (or not!). I can work out what the conditions are by creating 2 sets of 3 conditions (1 set if cell (RC) is empty and 1 set if cell (RC) has a date or "N/A" in it). My conditions are: 1. Turn the cell background colour red (action overdue) if: Formula Is:=IF(AND(RC[-1]"",RC="",RC[-1] 2. Turn the cell background colour orange (action required) if: Formula Is: =IF(AND(RC[-1]"",RC="",RC[-1]
View Replies!
View Related
Dates Distribution Between Time Periods
What formula should I use if I have a list of maturity dates and current investments amounts as of 06/30/07: 06/30/08 10,000 08/31/14 18,000 05/31/08 11,000 07/31/09 25,000 06/30/10 14,000 12/31/25 80,000 and the amounts need to be distributed in columns titled: Within 1 Yr (From 7/1/07 to 6/30/08) After 1 to 5 Yrs (From 7/1/08 to 6/30/13) After 5 yrs (After 7/1/13)
View Replies!
View Related
Add Sheets Based On Conditions
i am not able to solve this problem. 1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table 2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month. 3- Reference table has the list of the same data record ( numbers ) . Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following. a- Check for the record in the reference table , if doesnt exist then add the record in the reference table b- create another sheet same as the sheet " A" (duplicate of A) and name it the new record number b- Plug the new record number in the cell B2 of the new sheet c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to O8)from sheet A
View Replies!
View Related
FORMULA TO ADD AMOUNT BASED ON TWO Or MORE CONDITIONS
i need a formula to add amount based on two or more conditons. From the drop down list on each change i want to do the sum to be displayed in C18; see the attached file for reference. i can do the same thing using pivot tables but i want to the same to be done using the array formulas. Also tell me any other array formulas like this which are useful.
View Replies!
View Related
Convert Imported Text File For Calculations
I have a file with four sheets – File1, File2, Association and LookList. File1&2 are totalled, as is Association sheet but then are checked against each other using the totals. What I need to do is the following (but I am not sure how to do it using macro…) Step 1 - ‘File1’ Sheet – It all comes in text format. Firstly, convert all of column B=>used range (it may vary to 40k records!) into numbers and total each column. (I know the principal behind it – have a total variable for ColB, loop through and when it finds >0 then increment buy one and output under the last cell in that column – but I do not know the macro code) Step 2 - ‘File2’ Sheet – Again, it's in text format. Firstly, convert all of column B=>used range into numbers and total each column(would contain same amount of records as Step1!) . Step 3 - ‘Association’ – same steps as for File 1 (again – same number of records). Step 4 - THEN (and I have NO IDEA how to do this!...) I need to check the File1 & File2 against Association using the lookup and return the Serial number and the Association heading that does not match on a fourth sheet. The problem is that – for example – Field-1 consists total of more than one column – contains 3 but could contain a total of up to 6 columns! - I understand excel formula ‘vlookup’ but am not sure how to do it when it is a combination of columns and even less when applying it to this! – In fact, for a macro I am not even sure what column format the lookup list should be!
View Replies!
View Related
Convert Imported Values, Being Seen As Text, To True Numbers
I have a document passed to me that includes information pulled from an internal system. I want to use some basic formulas to show Totals, averages etc. However, the SUM function does not work on the data provided unless I select the cell (As if to edit the text) and the press enter. Is there a quicker way of selecting all of the cells in Column A for example and performing this function on mass, rather than selecting each cell individually. I have attahced a similar sample copy - My SUM formula is show in cell B15.
View Replies!
View Related
Imported Dates Being Seen As Text
The problem i'm having is that these data downloads come off the server like this: 01/14/08 so i'm getting the green triangle error "text date with 2 digit year" with the option to covert it to 19xx or 20xx. I have to manually select this text and select 20xx before it is recognized by excel for what it is. only after this is the conditional format rule processed, and my 5 day old dates turn their appropriate colors. When i set up a macro record while i do this date conversion, it does not record any command for this conversion. Is there any code i can enter that will automatically do this conversion process for me without having to do it by hand?
View Replies!
View Related
Remove Spaces From Imported Dates
I use a program that can export data into Excel. For the most part, it works fine. except it doesn't correctly handle dates. For some reason, when it pastes a date into excel it includes 8 or 9 (depending if the month is 1 or 2 digits) spaces in front of the date which makes calculations with these dates impossible unless you go into each cell and delete the spaces (or do Find / Replace). Is there a way I can create a non-VBA formula that will reference any one of these cells and some how identify what the date is & then that cell can be the date I can calc on? I.e., A1 is the date with the spaces in front, B2 is the cell that will reference A1 and produce, as the contents of B2, the date in A1. I can then use B2 in calculations. Note: the month formats possible are: M/DD/YY (Jan. - Sept.) & MM/DD/YY (Oct. - Dec.). There are no other variations.
View Replies!
View Related
HTML Text Imported As Dates
I have 85 Html files that I open in excel. The files have a bunch of columns with numbers. Excel handles most of them properly, but if the number looks like a date, it is imported as a date (which it shouldn't be). For example if the number is 13-1, excel handles it fine, but if the number is 12-1, excel thinks it is a date and imports Dec-01. How do I get excel to import it as 12-1?
View Replies!
View Related
Add / Sum Based On Dates
The object is to automate a weekly time sheet based on daily information. The "Daily Time" worksheet contains all 365 days of the year. The work week runs Monday through Sunday. Daily time is posted to the "Daily Time" sheet from handwritten time cards. The Weekly Time is then automatically calculated for each person and each category based on the dates in the "Pay Period From:" field and "To:" field. For example if 01/01/09 (Monday) is in the "Pay Period From:" field and 01/07/09 (Sunday) is in the "To:" field, then the "Weekly Time" sheet will total the hours posted between those two dates. In this manner there will be a continuous record of daily time in the "Daily Time" worksheet and the "Weekly Time" worksheet will reflect whatever dates are used in the two fields. I realize that because it is simply two dates, it is possible to show any combination of days. Basically what I need is to be able to add the hours between two dates and obviously the dates will change on a weekly basis. EXAMPLE IS ATTACHED
View Replies!
View Related
Date Formatting In VBA :: Dates That Are Imported As Text
to change the format of some dates that are imported as text. In a previous request ... [url] I was shown a way to convert dates correctly. This solution works perfectly when carried out manually, but doesn't work if a record a macro and then replay it - only some of the dates are converted. Here is a small sample of the dates I'm working with... 04/09/2008 05/10/2007 08/10/2007 14/09/2007 15/09/2007 17/09/2007 17/09/2007 17/09/2007 19/03/2008 20/09/2007 21/09/2007 21/09/2007 25/09/2008 27/09/2007 28/09/2007 28/09/2007 And here's the recorded macro, deliberately left exactly as is
View Replies!
View Related
Convert List Of Dates To Count No. Of Dates By Week
Basically I have 2 columns each with a list of dates in no particular order (and containing blank cells too), one planned date column and one actual date column. What I need to do is plot this on a graph, and since the number of dates has no set limit and I dont want to have to plot maybe 100 dates on the x axis, so i want to group them by week before plotting them, i.e. 10 dates for week ending 10th jan, 25 dates for week ending 17th jan etc I have a pivot table that counts how many of each date occurs, i.e. 10 x 2nd jan, 7x 3rd jan etc etc but it does not split them into weeks. im sure theres an easyish way of doing it so i can get the 2 lines on the graph for no. of planned dates each week and no. of actual dates each week, i just cant see it.
View Replies!
View Related
Convert Numbers Representing Dates To True Dates
I import data from my works server and dates are represented in text. example text format is yyyymmdd/19790310. What I'm trying to do is add and subtract from this date. I was trying to utilize formula and havent got close. I have a couple of parameters: In column L I would like to represent the following if column "B" is "A" add 38 years to column "J" dates are represented as TEXT if column "B" is "B" add 35 years to column "J" " if column "B" is "C" add 30 years to column "J" " if column "B" is "d" add 30 years to column "J" " Column "M" I would like to find the difference in column "L" and "C" I know days will be difficult it can be represented in years and months only. I have no preference on whether I use VBA or a formula but have failed on on formula and was attempting to use VBA Case method but no success. I have 10000 rows of information and am open to any ideas attached an example of my data.
View Replies!
View Related
Convert Numbers, Representing Dates To Actual Dates
I get a spreadsheet every day that has a column of numbers that contains dates that are not in a date format. Is there a way to convert these numbers to a date format? The numbers come from an outside source so I can't modify on the front end. look at the attached spreadsheet with representative data of what I'm trying to ccomplish.
View Replies!
View Related
Average With Multiple Conditions, Including Dates
My spreadsheet is too large to attach so I'll describe it as best I can. I have names in column H, which are repeated irregularly. I have corresponding grade numbers (1-5) in column I, and I have the dates they were entered in column J. It looks something like this: Column H Column I Column J Name 1 4 8/10/08 Name 2 3.5 8/11/08 Name 1 5 8/11/08 Name 5 4 8/12/08 Etc.... I want to average the grades (column I) for a certain name (column H) within the last 30 days, or however many days I enter. I'm using Excel 2003 with the analysis add-in. I can use SUMIF to include any two columns, but how can I write an average grade formula using both the name and date conditions? Dan Auto Merged Post Until 24 Hrs Passes;Come to think of it, all I need to do is sum the data. I have a denominator in a different box that I can use to average it. So in effect, I'm trying to creat a SUMIFS function.
View Replies!
View Related
Convert Dates To Text ...
I have a columns with dates such as 10/24/2007. The next column has a count of widgets made that day. I tried creating a column in between the two that would show only the Month and Year. I intended to base a graph on the converted dates and count, to show how many widgets were made a month. But the graph breaks down the count to the individual days, ignoring the conversion. Is there a way I can create a column that will literally take the date number and leave only a text in its wake? So that when I slect the latter two columns, the graph will only show the months and counts in that month?
View Replies!
View Related
Convert Dates To Text
I need to convert values in column A that Excel has erroneously formatted as date, to text. 4/9/5281 should be 5281049 (Excel dropped the zero in front of the 4. I need it back.) 14/9/5281 should be 5281149
View Replies!
View Related
Convert Dates To Another Format
I have attached a sample of the raw output of some call records from our telephone switch. My problem with the date data is twofold; first of all it outputs it in mm/dd/yyyy format, or so it appears. But when I try to change the format to mmm/ddd/yy Excel always interprets the 2nd pair of digits as the month instead of the first two. For example,the following 2 calls are from Jan 1st and Jan 2nd respectively; 01/01/2010 01/02/2010 but excel always treats the 2nd record as Feb 1st. I have read numerous posts from people with similar problems but I can't get any of their solutions to work with my data. The second problem is that on the 13 of each month the phone switch suddenly stops putting a leading zero in front of the date making the data totally unreadable to Excel apparently. Again, I read a post of a similar problem but can't get their solution to work. My ultimate goal is to reformat the date data so that it is displayed as; ddd mm/dd and leave out the year entirely. Ex. Fri 02/12 as in today...Friday, Feb 12th. I really don't know if the problem is with the data or me at this point.
View Replies!
View Related
Average Elapsed Time With Conditions Using Dates & Numbers
Dataset is 220K rows. A small subset follows. Using an array calc with criterions Day = 1 and Hour = 1 summed on Elapsed Time returns 81 with an average of 11.57 for 7 events of which there are 5 distinct dates. The function =SUM(IF(D2:D14<>"",1/COUNTIF(D2:D14,D2:D14))) returns the number of distinct dates in the dataset ie 7. Is it possible to combine these two methods to create an average of elapsed time by day and hour using a denominator of distinct dates for only that combination of dimensions? I have attempted to calculate an item in a pivot table using a different denominator without success. day Hour Date Elapsed Time 1 0 5/03/2009 25 1 0 5/03/2009 24 1 1 5/03/2009 23 1 1 7/03/2009 11 1 0 7/03/2009 5 1 1 8/03/2009 2 1 1 8/03/2009 8 1 1 9/03/2009 4 1 1 9/03/2009 17 1 0 9/03/2009 22 1 1 10/03/2009 16 1 2 13/03/2009 12 2 1 14/03/2009 15
View Replies!
View Related
VBA Code To Convert Dates
I received a solution to a question recently whereby I was instructed to use the text-to columns function to solve a problem I was having with a column of dates. Convert Dates To Another Format The solution provided was; Select Data > Text to Columns > Next > Next > Column Data Format > Date. The solution works fine when performed manually but if you perform this function while recording a macro, the crucial piece that changes the date output is not captured by the recorder so when you run the macro nothing happens. To expand on how I recorded it, after starting the text to column dialogue and pressing Next twice, the dialog provides me with a drop down selector where I am able to change the date format from the default DMY to the desired MDY which - when selected manually - fixes my problem. However, when I record a macro while performing this action the date format choice I made is not being recorded in the macro output so when I try running the macro later on the original data, nothing happens. The recorded macro ends up looking like this; ....
View Replies!
View Related
Convert Non-US Dates To US Date Format
I am parsing a string which contains (European) dates: "10/08/2005,11/08/2005,12/08/2005,13/08/2005,14/08/2005,". When broken into individual cells, dates before the 13th of the month are recognised as "American" dates and display as "08/10/2005", "08/11/2005", 08/12/2005" etc. with automatic date format. Dates from the 13th onwards are left in general format as "13/08/2005", "14/08/2005". I have written a macro that converts each date individually to its correct Excel serial number, but I can't help thinking that there must be a simpler way, and that I cannot be the first to have asked this!
View Replies!
View Related
Automatically Convert Dates To Xx07 Format
Is there a way to find cells that have a date in them that has a text digit with a 2 digit year to 'xx07' rather than having to click on the error and selecting it manually. it's playing havoc when I'm pulling some data from another workbook so converting en masse would sort the problem out! Anyone know how to do this?
View Replies!
View Related
Convert Dates To Text Format Using A Macro
Is there a way I can convert dates to text in a CSV file (opened via excel) using a macro? Currently I have excel recognised dates in the first column of the format dd-mm-yyyy and a corresponding values in the second columns. I know once I have the file open I can enter the following formula in an adjacent coloum "=text (A2, "MMM-YYYY") but I need to change the format in the existing date feild and not create a new coloum. Sure I could copy and paste the new formatted dates into the cell, but in terms of what I need to do, this is not practical as I several of these CSV files that I need to query and extract data from daily using a macro. The macro is not currently working because the date format is wrong. I need it to appear as MMM - YYYY. When you view the date in excel it must appear like this (as it would appear in say microsoft word) and not with the underlying date format.
View Replies!
View Related
How To Add Range From Multiple Sheets With Conditions
I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter. =SUM((start:finish!A2:A25="SU")*(start:finish!B2:B25="004*04")) gives me a #NAME? error. Example Data on each spreadsheet: A B SU 004*02 SU 004*04 SU 004*04 SU 004*02 SU 004*04
View Replies!
View Related
Selection, Convert And Add Column
I have a two vba codes. The first one defines the currency while the second one uses that info in order to convert them accordingly. I would like to edit the script in order to create a new column after my selection and then insert in each cell the proper currency as words (i.e $ -> USD, € -> EURO). So, if I select A1:A2 then only the cells B1:B2 must be converted into words. As an example:....
View Replies!
View Related
Convert .xla Add-in To .xls Workbook
I wrote a simple macro a few months ago and I got carried away and produced an .xla rather than a normal worksheet macro! I dont need to use this macro as often anymore and another member of staff will be taking the process over so I was hoping to turn the .xla into a normal worksheet macro. Unfortunately I am very confused by using xla's (at one point I had about 7 versions on my tools menu for some reason!!) and I cannot work out how you convert it!
View Replies!
View Related
Convert Multiple Rows To Columns And Add Column Headers
I'm currently faced with a spreadsheet that has data formatted like this: A 1 RandomRowofData1 2 RandomRowofData2 3 RandomRowofData3 4 RandomRowofData4 5 RandomRowofData5 6 RandomRowofData6 7 RandomRowofData7 8 RandomRowofData8 9 RandomRowofData9 Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words).... I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.
View Replies!
View Related
Lookup Based On Two Conditions ...
I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1. The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H. So I need to return something like this: ...
View Replies!
View Related
Get A Result Based On Several Different Conditions
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in. The following is a table which shows a salesmans sales. He or she has sold to 8 customers. What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria. 1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40 2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25 3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
View Replies!
View Related
Display Value Based On 2 Conditions
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course. The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
View Replies!
View Related
Lookup Based On A Few Conditions
I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).
View Replies!
View Related
Lookup Based On 2 Conditions
In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.
View Replies!
View Related
Sum Based On 2 Conditions
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime. Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
View Replies!
View Related
Add Dates In Subsequent Worksheets
I am building simple worksheets that consist of weekly time sheets. In cell B7 is the week ending date for that week. When I copy the worksheet onto a new worksheet, I want B7 of the new worksheet to be the week ending date 7 days later.
View Replies!
View Related
|
TRACKER
