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 2 Replies
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.
look at the attached file - it was a CSV file. i want to convert the column of dates to say Mar 14 2009 type date. but it only converts some of them.
note some are on the left and some on the right.
I am in strange situation where I have a data with Invoice Dates. These dates are in Text. However, when I convert it into reall excel dates. For some reason the dates do not come right. convert my text dates into excel real dates. I did a lot of google search and apply these three methods but all of these gave me strange result I was not expecting. These are the methods I used and the result of each method.
Method 1 : Using Text to Column Wizard (Excel 2007)
I highlighted invoice column and went into text to column wizard. I clikced on Date button, and selected MDY format. Strangely enough, my result was day, month and year i.e. DMY which does not seem right.
Then I press Control ~ on this data as shown in second view.
Inv DateMDY09/22/1122/09/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/201108/31/1131/08/2011
control ~ (overview)
Inv DateMDY09/22/114080808/31/114078608/31/114078608/31/114078608/31/1140786
2nd Method - Using formula to convert text date to real dates When I used formula, I get the following result. As you can see, formula is converting text dates into different system dates than the first method.Further strange things is when I take these system dates i.e. 4283 and in 2007 excel format use "Short Date" the same system date gets converted into wrong year as shown in the second view.
Inv DateFormulaFormula Result09/22/11=DATE(MID(B2,7,2),MID(B2,1,2),MID(B2,4,2))428308/31/11=DATE(MID(B3,7,2),MID(B3,1,2),MID(B3,4,2))426108/31/11=DATE(MID(B4,7,2),MID(B4,1,2),MID(B4,4,2))426108/31/11=DATE(MID(B5,7,2),MID(B5,1,2),MID(B5,4,2))426108/31/11=DATE(MID(B6,7,2),MID(B6,1,2),MID(B6,4,2))426108/31/11=DATE(MID(B7,7,2),MID(B7,1,2),MID(B7,4,2))4261
2nd view - system dates are getting converted into 1911
Inv DateFormulaFormula Result09/22/11428322/09/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/191108/31/11426131/08/1911
Similarly, I tried other methods or copying blanks etch but none seems to convert text dates into real (system dates).
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.
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
I have a column in my data set that consists of dates and times in this format: 2014/08/02 01:46:49 PM. I am trying to convert these dates and times to numbers so that I can actually use these values for calculations and regression analysis. When I click on one of the cells, I get a number that is revealed along with decimals. For example, the cell with 2014/08/02 01:46:49 PM had a stored value of 41853.574. I tried to highlight the entire column of dates + times and click on the format cells button. I selected the number category under number but that did not work. I also tried to use the =DateValue(Cell #) function but that did not allow for distinguishing between the same date but different times.
What are my options for converting these dates and times to numbers that I can work with? Is there a way to get the entire column of dates + times to show each cell's numeric stored value?
1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
I have a long column of dates YYYYmmdd and I need to be able to convert them to mm/dd/yy so that I can see (for example) 09/18/72 instead of 19720918
View 9 Replies View RelatedI need to count the actual number of days between 2 dates but excluding Sunday...
For example,
Start Date-----End Date-------# of Days
01/Jan/14-----23/Jan/14-------20 days (excluding Sunday)
In some cases, Holiday also to be removed i.e. 01-Jan-2014 is holiday, then the # of days should be 19 days.
If I use Networkdays formula, then system results excluding both Saturday and Sunday. But I want to remove only Sunday.
I am trying to create three different conditional formats for my date tracker. I have attached an example of what I am trying to accomplish and tried to write out what the rule's are, just struggling on how to write the formulas.
Condition 1 - If the cell in column C's date is past due, change the cell red only if there isnt a date in the "Actual" column B
Condition 2 - If the cell in column C's date is coming up in the next 30s change the cell to yellow only if there isnt a date in the "Actual" column B.
Condition 3 - If there is a date in column A and Column B turn column C's cell green.
I am using this formula "=+IF(B3="",A3,B3)" to populate Column C.
Conditional Formatting.xlsx
I am creating a tracker to record, incoming, latest date of reply needed and actual date of reply. So I have column A= received, Column B = need to reply by, Column C = replied on. What I want in A is today’s date which is easy enough with ctrl+; is there a way of having it set that as soon as the cell is clicked on that date is activated and until then it remains empty?
What I have in B is A# + 20 this adds 20 days to the date entered in column A, again is there any way to have it set that this automatically activates when cells in column A are active and not until then, as if I enter that code in the cell and nothing is showing in cell A it gives a date of the 20/01/2008.
In C I have tried a couple of codes and they have not worked so what I would like is a code that when A and B are active it generates a countdown for days left to reply and if this countdown reaches zero then the whole line is flagged up in red as late. Lastly, is there a code that I can use that will automatically correspond with the A, B or C columns and match them with the same number cell in the other columns, sorry this isn’t so easy to explain but what I have to do at the moment is type in B is =A1+20 , A2+20, A3+20 and so on rather than just typing in A……+20 for column B to know it needs to associate itself with the same number cell in column A regardless what number it is.
I am trying to compare the difference between to dates (a person dob and the date of an activity they have carried out and then output the age of the person when this activity was done:
for example:
activity date: 08/01/2008
dob: 05/12/2007
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?
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
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.
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; ....
I have a spreadsheet with reference relating to dates that are listed as single days. I am trying to convert the single dates relating to a reference to a from and to date but i'm having problems.
Example
048051_14110/09/2013
048051_14111/09/2013
048051_14112/09/2013
048051_14113/09/2013
041929_14115/10/2013
041929_14116/10/2013
041929_14117/10/2013
041929_14118/10/2013
I want this to read
048051_141from - 10/09/2013 - to - 13/09/2013
041929_141from - 15/10/2013 - to -18/10/2013
i have messed up column A which has only dates and i have different date formats like 03/07/2013 and 07/03/2013 i need to have a fixed date for all in B like 03 July 2013
View 2 Replies View Relatedhow to convert dates to text without using addon, using VBA. like 23/12/2013 to Twenty third december, two thousand thirteen,
View 1 Replies View RelatedI am trying to convert text 'dates' like Sat. 1/05/2007 to real dates. I need to find all 'date' cells in range B4:B100 (they are filled in yellow), strip off the offending text (i.e. Sat.), and use DateValue to turn the rest into a proper, numerical date in the ddd mm/dd/yy format, arial, bold, 10pt, and centered in place.
I've spent days trying to write this macro. I've had help from experts in forums, but whenever I need to make any slight modification, a trainwreck ensues. Please see the code below.
I have a column wherein the dates are treated as text , i want to convert that into dates.
View 9 Replies View RelatedI 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!
When I import data , the dates appear as follows in Col G sheets "Imported Data"
If I select Col G and use Text to columns and select mdy, it gives me the dates in the correct format. However, If I use the macro recorder to do this and then run the macro, it does not work
I have attached the dates after using text to Columns wjhich is the correct format.
Imported Assets *G207/01/2006302/01/2007402/01/2007511/01/2008601/01/1985720/11/1990801/01/1985901/01/19851001/01/19851101/01/19851225/09/19921302/01/1993
Imported Assets  G201/07/2006301/02/2007401/02/2007501/11/2008601/01/1985720/11/1990801/01/1985901/01/19851001/01/19851101/01/19851225/09/19921301/02/19931401/05/19931501/06/19931601/08/19931701/01/19941801/09/1995
I am having an issue with converting what seems like Text to Dates. I am trying to group dates in my pivot table, but I keep getting the error "cannot group this selection". My dates are left justified, causing me to believe that they are text.
When I format the cells, however, the cells say that they are already in date format. When I try using Text to Columns in the Data Tab and selecting MDY, I get the error "You cannot move a pivot table table report or insert worksheet cells, rows, or columns."
Not sure what to do from here, as I would like to group dates by weeks.
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.
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?
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
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012Â 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View Related
