Converting Dates- Dates YYYYmmdd To Convert Them To Mm/dd/yy
Feb 19, 2008I 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
ADVERTISEMENT
Converting Dynamic Dates To Static Dates?
Jun 15, 2014Today() provides a dynamic date which always equals the current date - not very useful when dating accounting documents. Is there a function that will allow the use of Today(), at the time of the document creation, but will then freeze the date to the date of creation?
View 5 Replies View RelatedDifficulties With Dates (convert The Column Of Dates To Say Mar 14 2009 Type Date)
May 3, 2009look 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.
Excel 2007 :: Unable To Convert Text Dates To System Dates
Oct 21, 2011I 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).
Convert List Of Dates To Count No. Of Dates By Week
Jan 27, 2006Basically 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.
Convert Numbers, Representing Dates To Actual Dates
Oct 15, 2009I 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 View RelatedConvert Imported Text Dates To True Dates
May 18, 2007I 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
Convert Numbers Representing Dates To True Dates
Jun 29, 2008I 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.
Converting Dates Into Excel Dates
Sep 15, 2009I need to convert the following string into Excel dates:
Jan 1, 2009
Jan 12, 2009
Jan 21, 2009
Right clicking it and changing it to date format will not work.
I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.
Convert YYYYMMDD To DD/MM/YYYY
Aug 24, 2007My spreadsheet imports a date field and displays it as YYYYMMDD.
In A2, I use the formula =Right(a1,2) & "/" & Mid(a1,5,2) & "/" & Left(a1,4) and this converts it to DD/MM/YYYY
However, I can't find out how to write this into my VB Macro.
I've tried:
Range("a1").FormulaR1C1 = "=RIGHT(RC[-1],2) & ""/"" & MID (RC[-1],5,1) & ""/"" & LEFT (RC[-1],4)"
Converting Dates To Cell Value?
Aug 20, 2014Backstory: I am converting PDF documents to Excel files and some of the numbers are being transformed into a wonky date format which to the ***** eye looks semi correct but the underlying value in the formula bar is incorrect. Basically I need to copy and paste the value but when I paste special values it doesnt work. Below is the example
The cell value should be: 9856 When it is converted to excel the value shows 98 5 6 but in the formula bar it shows: 5/6/2098 The cell is formated as: Custom yy m d;@
If I get the value in the cell to read 98 5 6 I can use trim or something to get it down to 9856 but the issue is when I use those functions they return 72446. I guessing this is because they read the formula value not the cell value.
I need to somehow get the value in the formula bar to read 9856.
Converting Dates To Weeks
Jun 27, 2013Is there a way to convert data that has dates to week by week? such was week 1 week 2 week 3 etc....
View 8 Replies View RelatedConverting Dates To 8 Digits
Jan 12, 2006I have dates being imported (AS TEXT) to excel in various formats and want them all to appear in their cells in the same 8 digit format (MMDDYYYY) so it can be exported to another program
for example:
Single Digit Month and Day
cell A1: 3179 (convert to 03011979)
cell A2: 30179 (convert to 03011979)
cell A3: 030179 (convert to 03011979)
cell A4: 311979 (convert to 03011979)
cell A5: 3011979 (convert to 03011979)
cell A6: 03011979 (already correct)
Single Digit Month and Double Digit Day
cell A7: 52165 (convert to 05211965)
cell A8: 052165 (convert to 05211965)
cell A9: 5211965 (convert to 05211965)
cell A10: 05211965 (already correct)
Double Digit Month and Single Digit Day
cell A11: 12523 (convert to 12051923)
cell A12: 120523 (convert to 12051923)
cell A13: 1251923 (convert to 12051923)
cell A14: 12051923 (already correct)
Double Digit Month and Double Digit Day
cell A15: 112180 (convert to 11211980)
cell A16: 11211980 (already correct)
Converting Dates To Weeks
Jul 20, 2006I require a worksheet function or group of function that will allow me to
convert my dates of format dd/mm/yyyy to Week 1, Week 2, Week 3, Week 4.
Converting Strings To Dates
Jul 15, 2008converting text strings to dates.
The dates I have imported into a spreadsheet are of the format:
11.05.08
I use code to convert it to this format
11/5/08
But with some of the dates, I get
11/5/2008 whereas with others, I get
11/5/08
However, if I recorded a macro the date format appears as
11/5/2008 for all the dates.
But the code is excatly the same, if I
see below:
My
For Each celld In Range([e1], [a1].End(xlToRight))
If celld.Value Like "*DATE" Then
celld.EntireColumn.Select
On Error Resume Next
Also, how do you convert strings to dates using VBA?
I thought DATEVALUE performed this function but keep getting errors when i try using it.
Converting Non Standard Dates
Feb 13, 2007I'm a moderate user of excel with some experience but need help creating a specific if statment.
I have a column "A" that shows dates formated like this: 060115 (January 15, 2006). Because excel does not recognize this as a valid numerical date I need column B to show what year the date falls under. To complicate it I need it to reflect a seasonal year from April 1 YYYY, to March 31, YYYY.
So here's an example. Say I have a date in column "A" of 050612 (June 12 2005). I need column "B" to look at that date and determine if it's >040105 and <033106 and return a result of "2005". I then need the formula to calculate an answer for up to 5 years worth of returns.
how to set up this type of =IF statement?
Mass Converting Values To Dates
Sep 8, 2009I am working on combining several Excel spreadsheets and a report from a different program into one master Excel report. I have a macro built so the report can be compiled efficiently every quarter. However, I have one obstacle to overcome before I reach report programming bliss: The other program's report does not use a date format that Excel can interpret as a date. Excel interprets it as a value.
Specifically, the other program outputs September 3, 2009 as 9032009. It is crucial that the report be arranged in chronological order (time is a separate field). Does anybody know if there is a way to mass convert a bunch of values that look like 9032009 into 9-03-2009?
Converting String Dates From UK To US Format
Oct 4, 2007I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:
Simple example of the problem:
Sub test()
Dim testy As String
testy = Now
'testy will equal 04/10/2007 17:20
Range("A1").Value = testy
'A1 will equal 10/04/2007 17:20
End Sub
Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.
Converting Numeric Data To Proper Dates
Jan 4, 2014I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.
I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.
The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.
Converting Dates To Show Month And Year?
May 8, 2012i'd like a formula to change a date to month and year
Column E
11 May 201123 November 201108 July 201103 September 201111 March 201224 December 2011
I've used =Text(e1,"MMM") to pull the month through but would like to include the year too.
Converting American Dates To European - Non-formatting
Jan 27, 2007I get sent a spreadsheet by one of our partners where the dates look like they are in European date format except thay are actually in American format.
example
01/09/2007 - they mean 9th January 2007 but Excel holds 39326 which is 1st Sept 2007.
I can obviously format them but when I sort the list Excel still reads 1st Sept instead of 9th Jan
There are over a thousand entries so re-typing isn't an option.
Stop CSV File Converting Numbers To Dates
Jan 14, 2010I'm having a little problem working with some CSV files that are sent to us by an outside party. There is a field for an invoice number. This number should be a string since it sometimes has alpha characters and sometimes has dashes. But the field is not delimited to indicate this, so when opening these files in Excel, Excel will sometimes convert the numbers to dates. Once they are converted to dates, there is no going back.
The way we have gotten around this is to change the csv extension to txt, then open as a txt file and convert that particular column to a text column. This causes a lot of additional work as the file ultimately needs to be converted back to a CSV.
Ideally, the people creating the CSV files should create them so the invoices column is properly delimited. But that doesn't seem to be possible right now.
Is there a way to create a macro that would make the modification while opening the CSV file, before Excel has a chance to modify those fields?
Stop Excel Converting Numeric Values To Dates
Jul 9, 2012I have looked at some threads related to this issue but don't quite fit what I am on about. I access some data online that appears in a grid format which I copy and paste to Excel. I don't have access to the source data/database. I select and set some query variables and run the query and the result appears in the grid. I copy and paste to Excel that is it. When paste to Excel 2010 I have the choice between "Keep source formatting" and "Match destination formatting". OK, I always choose the 2nd option i.e. "Match destination formatting". That is it. It appears to be a simpler format. Where does the problem come in? Can you help me stop Excel from involuntarily converting numerics to dates when not asked?
The data itself can (at least the part that I'm interested in can) come in 2 different ways. 1) WPS or 2) W%P%S. In case you're interested these represent racing statistics where 1) WPS = Number of Wins, Places, Starts given as whole integer format and 2) W%P%S = Percentage of Wins, Percentage of Places and S is still the integer number of starts. Now, when I run the query and copy these statistics over to Excel, particularly in the form of the 1st type of data i.e. WPS they are in the form e.g. "2-6-12" which means 2 wins, 6 places from 12 starts. The stats literally come across in the form "W-P-S" where I fear the delimiter "-" is causing the date conversion consequences that I have outlined at the start. ALSO, as you could have imagined, I have tried importing the data in terms of the 2nd type of data namely "W%-P%-S", which, on the surface of things begins to solve the problem in terms of the initial paste of the data into Excel. That is true, and yes I have found that this has delimited the data correctly mainly. Although there are still occasional problems. When I import the data this way, I still want the data in the % form as well as the integer form. So I do a great deal of data massaging with what I have and yet in re-converting some of the data back to integer form I can still encounter the "convert to date" problem.
Stop Excel From Converting Football Scores Into Dates
Jan 11, 2009http://www.excelforum.com/excel-gene...cognition.html
and im copying and pasting data from a website ( football scores )
and when i get what should be 1-1 it returns 01-jan and this i dont want
i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages
wondered if anyone could work out some syntax to use as a macro button?
claymation had a go but it doesnt work.
Excel 2013 :: Converting List Of Dates To Days
Oct 21, 2013I have a list of dates and want to convert them to something like Monday 21st October instead of 21/10/13.
I am sure this is easy but cannot see how to do it. I am on Excel 13.
Compare Two Dates And Convert To Age
Jul 17, 2009I 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
Convert Dates To Text ...
Sep 12, 2008I 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?
Convert Dates To Text
Nov 10, 2009I 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
Convert Dates To Another Format
Feb 15, 2010I 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.
VBA Code To Convert Dates
Feb 15, 2010I 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; ....