American / English Dates
Feb 7, 2008
I have US reporting package that e-mails a report, which I then run macros to put extract the required data, and pivot etc...
There can be anwhere between 1 and 15,000 lines of data.
The file is generated as a CSV file, with dates in the first column in the format mm/dd/yyyy. Other header information also has a "/" so it is impossible to do a text to columns.
I need to continue using UK dating format, so cannot change the Excel settings.
Can anyone provide me with VBA code to convert mm/dd/yyyy into dd-mmm-yyyy without it ignoring any dates where the dd is less than 12?
View 9 Replies
ADVERTISEMENT
Jan 27, 2007
I 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.
View 9 Replies
View Related
Dec 23, 2013
how 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 Related
Jun 21, 2007
I have a csv file which has been output by an American system that I use, the text that is output is one column is as follows:-
6/19/2007 10:24:44 AM
2/20/2007 11:01:37 AM
2/27/2007 8:18:58 AM
I would like to learn a formula which re-arranges the date to a UK format so that I can do more detailed analysis of data. If possible I would like to strip out the time part of the text in one.
View 3 Replies
View Related
Dec 9, 2009
I'm currently running a macro which copies data from one sheet and pastes it to another.
One of the fields on the primary sheet is a date in the english format DD/MM/YY but when it pastes onto the second sheet it becomes a Julian date 39820 instead of 01/07/09, and when this figure is converted back to date format it becomes american format MM/DD/YY
Currently I've set the macro to PasteSpecial xlValue
How can I make it paste into English format?
I'm using excel 2003
View 9 Replies
View Related
Jun 3, 2009
I have excel 2007 and how to do a text split. For example, in A1 I have : Name American Company AP Code PI Due $0.00
Is there anyway to do a text split in B1 to just have:
American Company
View 5 Replies
View Related
Jul 20, 2009
Got a random problem with the IsDate function and was wondering if anybody could help?
Below is the code I am using to check a user input of a date, and if it is a recognised date put it in to a standard format (dd/mm/yyyy). The problem is that VBA is checking for an American ...
View 7 Replies
View Related
Oct 12, 2006
are there any easy way to reformat a fractional number: I need, for example, to make 5,23 look 5.23. Is there any function in Excel? I guess not, but have no clue where to start my VBA code.
View 6 Replies
View Related
Mar 8, 2012
So I have table that consists of several columns describing a single transaction. I want a macro to translate the (potentially thousands) of rows into a single cell for each row on another worksheet.
The rows need to be formatted by transaction type. The problem is that I know nothing of excel syntax .
I was thinking something along the lines of a case loop, if possible:
Case D1 = BUY
Output("You bought ("C1") on ("A1") for the amount ("E1")")
Case D1 = SELL
Output("You sold ("C1") on ("A1") for the amount ("E1")")
etc.
How would I get that into workable excel code, if possible?
View 2 Replies
View Related
Mar 26, 2006
I have excel files which has text written in deutsche(german).
am trying to write a code which can convert this text to english.
have also found a reference from this forum
[url]
But this doesnt seems to be working for me.
View 9 Replies
View Related
Jun 26, 2014
I want to convert all the Arabic letters into English (ie. Translitertaion) Not translation. I have found the below sample code in google and I have just tested but it is not working
Const cyr = "123456789" 'the cyillic alphabet in order
Const lat = "abcdefghi" 'the latin alphabet in the same order
Function convertchar(inchar As String) As String
Location = InStr(cyr, inchar)
[Code]....
If I type any number in cell A1 and run the above convertcells, it is not converting to latin.
View 1 Replies
View Related
Nov 8, 2013
I have an excel file which I really need to encode non english characters as when I import it into our system all special characters return as �
The problem is I need it to work similar to how this works: Unicode to HTML converter
It only encodes standard text, leaving the HTML intact, and only converts non-english characters such as UTF-8 etc...
I have tried setting the encoding when saving to a CSV file as UTF-8 however still no luck.
View 9 Replies
View Related
Oct 16, 2006
How to display following, that the day and month will apear in English, w/o relation to current regional settings:
Format( Date, "Long Date") & ", " & Time
View 9 Replies
View Related
Jul 28, 2008
a2 needs to contain a number (multiple of 10 10-90) and then d2 must contain a formula that gives that number in word form.
I need to do the same thing in b2 and e2, only with a number 20-99 not neccesarily ending in 0.
View 9 Replies
View Related
Nov 6, 2013
I have several graphs in excel which has the percentage number as follows: 15,5 %. I want to convert the number to 15.5 % so it is in line with the english language.
View 1 Replies
View Related
May 7, 2014
I would like to share with you this VB Code
convert number to text ( Convert Numbers to Text.) I hope you like it
Add it to add-in to be able using it in all excel sheets.
View 1 Replies
View Related
Feb 6, 2014
I have a formula as follows:
Code:
=TEXT(A1,"Mmm YY")
Where cell A1 contains a date, say 01/01/2014.
For me this returns "Jan 14." However, I have a colleague using a Spanish version of Windows/Excel and therefore it displays this in Spanish as "ene 14."
How can I force the formula to return the English spelling, Jan 14?
View 2 Replies
View Related
Feb 28, 2007
A workbook was created using the English version of Excel. It uses Analysis Toolpak functions.
When the workbook is opened on a Spanish version Excel system, embedded functions - like IF, AND, OR translate to Spanish. Other functions, like NETWORKDAYS, do not.
Any cross-language users have a tool for this? Other than doing a Find/Replace or creating custom functions.
View 9 Replies
View Related
Apr 30, 2008
A co-worker has an excel file that has both English and Chinese words in it. We need to convert the Chinese text to English. The contents of the files are sensative so I cannot share a copy. how to correct this?
View 2 Replies
View Related
Dec 5, 2012
I run excel 2010... I have xls file (see file attached) with both English and Non English characters.
When I save this file as xls or xlsx - everything is good, but when I save the file as CSV and try to open it later - I see that the English characters stays the same but the non English characters become gibberish.
How can I save a file (that include some non English characters) as CSV without loosing the non English characters? Is there a way to do that from within the excel 2010 menus? Or maybe there is an external tool?
Attached file : 913365454523.xls‎
View 1 Replies
View Related
May 19, 2008
The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...
View 9 Replies
View Related
Apr 30, 2013
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
View 3 Replies
View Related
Jun 17, 2007
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
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
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
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
View Related
Feb 23, 2014
Here is my set up:
A2 to BF2 is a range of dates
A3 to BF3 are sales. Days without sales are 0.00
I want to pick a range of dates and find the number of days without sales between those dates. So, a formula that will look to a start date in A1 and an end date in B2, and then count the number of days that did not have sales between. Index/Match/Countif/Dateif I can't seem to make anything work.
View 3 Replies
View Related
May 14, 2009
Hi Guys, This has been bugging me for a bit now and I just can't sus it...
I have a sample perpetual calender that I have been modifying to fit my own purpose. The calender part works fine.
I have beside that a column for holidays, etc and then a another column for other events.
When I put the date in the holiday or events columns I would like the date to be highlighted in the calender above (different colour depending on which column it came from).
The formula I have been playing with (no success) is:
=MATCH(DATE($R$2,1,C8),$I$41:$I$65,0) - This is the Formula for the 1st column of dates.
The 2nd formula is similar, just changes the column it tries to draw the MATCH(DATE.... from...
Although this formula works fine on the sample spreadsheet. When I enter the formula on my sheet, it doesn't seem to work...
I have attached the spread sheet that I am working on.
View 2 Replies
View Related
Apr 4, 2008
there are a multitude of issues with US date formats when you're not in the US but I've run across one that I can't figure out.
I have a source table that has approx 5000 lines on it, everything looks correct and all the dates are in the correct (Australian) format. However when I use it to make a pivot table, any dates that are before the 12/m/yyyy gets changed to the mmddyyyy format in the pivot table only, all the source dates are still correct.
View 3 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
