Excel 2007 :: Unable To Convert Text Dates To System Dates
Oct 21, 2011
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).
View 4 Replies
ADVERTISEMENT
Aug 11, 2014
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?
View 4 Replies
View Related
May 18, 2007
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 6 Replies
View Related
May 3, 2009
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.
View 5 Replies
View Related
Jan 27, 2006
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 13 Replies
View Related
Oct 15, 2009
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
View Related
Jun 29, 2008
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 7 Replies
View Related
Sep 12, 2008
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 9 Replies
View Related
Nov 10, 2009
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 9 Replies
View Related
Feb 19, 2008
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 Related
Jul 21, 2008
I 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.
View 10 Replies
View Related
Feb 12, 2009
I have a column wherein the dates are treated as text , i want to convert that into dates.
View 9 Replies
View Related
Jun 26, 2014
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.
View 9 Replies
View Related
May 16, 2007
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 9 Replies
View Related
May 30, 2014
I have a Billing Data where i want to Calculate TAT between two dates in Excel 2007.. I received the Invoice from Vendors on specfic date but due to discrepances, i rejected the invoice, after few date i received a rectified Invoice from Vendor... so how can i calculate the TAT from 1st Receipt to Rejection..
Column A Column B Column C Column D Column E Column F
(Receipt Date) (Rejected Date) (Re-Receipt Date) (Rejected Date) (Final Receipt Date) (Formula for Finding
Rejection TAT)
E.g : In Column F, TAT to be calculated from 1st Receipt Date to Rejection Date, but if i received the rectified bill & after inputing the Re Receipt Date in Column C, then the Cell in Column F will remain blank, but again if it is rejected due to some error, & after mention the 2nd Rejection Date in Column D, then the TAT to be calculated from 1st receipt to 2nd Rejection (Column D), but if i received the Final Rectifed Invoice & after mentioning the Receipt Date in Column E, then the Cell in Column F will remain blank.. but pls note, if the invoice is not rejected the, the Cell in Column F will remain blank..
I have tried IF Forumula but in vain.. i can only nest 1st receipt to 1st rejection i.e : =IF(AB6= "", "",(TODAY()-AB6))
View 10 Replies
View Related
May 30, 2012
In functions it is easy to simply say =if(a1>b1, do X, don't do X)
But how do I do this in VBA? Excel 2007
View 2 Replies
View Related
Nov 7, 2013
I am using Excel 2007 and am having some trouble formatting a column for dates. I am setting up a template spreadsheet for the company I work for. The template spreadsheet has a sheet for entering the raw data and a sheet for summarizing it. I need new dates that are to be entered to be formatted correctly. I have set up a sample spreadsheet and inputted some sample data which looks like the following:
Date
06.11.13
07.11.13
08.11.13
05.10.13
06.09.13
Now if I format these cells as English(UK) dates with the "dd.mm.yy" format the data>sort cannot sort these dates from oldest to newest. I can correctly sort them by using "text to columns" however if i do this then it only format's the cells that currently have a date entered into them and not any new date entries.
So in summary: Format cells into a date format does not allow dates to be sorted using the data>sort tool. (I assume that as they cannot be sorted ,a formula that searches for cells with dates before a certain date will not work either) Text to columns does not allow new entries to be formatted in the same way as the cells that had data in them when the text to columns tool was used.
Is there a way to format blank cells so that they will recognize the data inputted as a date?
View 2 Replies
View Related
Mar 19, 2012
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].....
View 1 Replies
View Related
Feb 28, 2007
Ive got an example below of how i'm sorting fixtures and how to determine winners/losers and draws.
******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF1G1H1F2G2H2F3G3H3F4G4H4=
ABCDEFGH102/03/2007Team*A**Team*B000202/03/2007Team*C**Team*D000309/03/2007Team*A**Team*D000409/03/2007Team*B**TeamC000Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What I'm struggling with is the ability to change dates and still be able to add the teams figures up.
When adding, for instance, Team A's wins I'm using
=f1+f3
and similar for defeats and draws.
But when I change dates, for instance game 3 to the 10th, Team A is still counting F1 and F3 yet its games now in row 1 and row 4.
How can i get it to change with the sorting.
Im sorting fixtures via the sort feature in data.
View 9 Replies
View Related
Aug 8, 2012
I have a stripped down data source for debugging purposes. I only have 4 rows of data for test purposes. The dates are formatted as dates.
When I create the pivot table the dates become my column values. When I select the first date in the pivot table the Group By Field menu option is grayed out. I tried setting a tabular format but didn't work.
View 9 Replies
View Related
Nov 22, 2013
Can I use dates as argument in Boolean arithmetic? I have a list of name with their date of birth and I would like to tell who is between 18 and 25. It's easy enough with number but with dates? Excel 2007
View 9 Replies
View Related
Jan 20, 2014
I am setting up a Cattle management system in excel 2007. In the column F are the date of births for each individual animal. Example: F2 13/03/2013 F3 23/05/2013. I would then like to highlight the cells with the dates in to find all the cattle that are under the age of 16 months from the current date.
View 9 Replies
View Related
Aug 21, 2013
I have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
View 2 Replies
View Related
Jul 24, 2014
What I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then _
Target.Offset(0, 14).Value = Now
End If
End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
I am using Office 2007.
View 1 Replies
View Related
Dec 7, 2012
We are using Excel and Outlook 2007 and 2010 in our office and this program needs to work with both versions of each, Excel and Outlook. I need to have a csv file exported from outlook to identify locations of meetings and when they occur. I can do this manually but it could be used at multiple times by different people
View 4 Replies
View Related
Nov 8, 2012
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
View 1 Replies
View Related
Nov 14, 2013
I would typically consider myself a decent Excel user, but I haven't been able to solve this one. Maybe it's just the lack of sleep now. I am attempting to combine 4 cells into one where the last cell contains a date. Below is the formula I am using:
=B3&" "&C3&" "&D3&" "&TEXT(E3,"m/d/yy")
It works when all of the cells have values, but the only problem I am having is that some of the dates (in column E) are blank. When this cell is blank the default date that displays is "1/0/00." If I add the typical, " " at the end it says the formula has errors. The need for the TEXT(E3,"m/d/yy") for the cells that contain dates is throwing me off.
View 3 Replies
View Related
Feb 13, 2008
I have a WAY to complicated excel form that I'm going to move to Access but I need to have this working in the interim. The form has a dynamic element, the user can choose the number of items they are ordering and it unhides the required number of text boxes. To keep the scripting down a did a little work-around to update the database using a loop through the form controls:
'Save the main row
MainOrderRow = ActiveCell.Row
LastRow = MainOrderRow + (ExtraOrders - 1) ' Calculate the last row
ExtraOrderNo = 1
For DBRow = MainOrderRow To LastRow
' Save the text box name with the extra order number
k = "txtDescriptionMul" & ExtraOrderNo & ""
l = "txtQuantityMul" & ExtraOrderNo & ""
m = "txtTotalValueMul" & ExtraOrderNo & ""
n = "ComboBoxUnitMul" & ExtraOrderNo & ""...................
View 2 Replies
View Related
Aug 9, 2008
I have a macro that searches and replaces dates (both text and date formats) in all files in a folder. It works if I initiate it from the VBA Editor, but not if I initiate it from the file.
It seems that the worksheet is not activating. Please take a look and let me know what I can try.
Things I have already tried: Window methods, such as minimizing the workbook that contains the macro, activate next, etc. Also putting the macro in Personal.xls and retrieving the values from the other file.
Where it hangs is right after the first workbook in the folder is opened. I tried to get it to select A1 in the active sheet but that cell is not selected in either the macro workbook or the newly opened workbook.
Also, if there is an easy way to exclude my macro file (name contains ZZZZ) from the FileSearch............
View 9 Replies
View Related
Aug 24, 2012
I am working with the Ganntt chart and horizontal bar charts but can't seem to figure out a way to force the X-Axis to behave properly.
In Excel 2007, I am trying to get the X-Axis to show major units of Months. However, in my chart options I can only change the Y-Axis to be Date/Monthly.
Changing my data layout (from the below) to be a vertical format produces the same problem, just in the other direction.
My data looks as follows:
System
Blocker
Production
Migrataion
Retired
Sys1
1/1/2012
780
Sys2
1/1/2012
400
60
90
Sys3
2/1/2013
30
5
1
The Blocker column is formatted as "No Fill" in order to cover a portion of the time-scale.
My X-Axis displays as random dates throughout the period and all of my data displays properly in terms of the scale on the grid.
What is NOT working is that the dates shown on the X-Axis really need to be based on a Monthly scale instead of the randomly selected dates Excel is using.
20120823.xlsx
I can't seem to find the right combination of options to make that happen or force a scale on the X-Axis.
View 1 Replies
View Related
