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?
I am using a MID fn to extract a single digit from a numeric string. I then use an IF Function to return a value based on the extracted digit. My IF statement works fine on manually entered digits but doesn't recognize my MID fn result. What am I missing?
View 6 Replies View RelatedI have a customised excel woorkbook that will be distributed to several locations with different computere systems, etc. I wanted to keep some convention with the naming, so I've put in a saveas routine to open the saveas dialog and change the default name. The user can then select the directory location and save the file.
Here's the
Sub Saveas_routine()
'
Dim strName As String
On Error Goto InvalidName
strName = "E-RAMP " & Sheet1.Range("A1") & Format(Now, " dd-mmm-yyyy") & ".xls"
Application.GetSaveAsFilename (strName)
Exit Sub
InvalidName: MsgBox "The text: " & strName & " is not a valid file name.", vbCritical, "E-RAMP"
End Sub
The problem is, when the save button is pressed in the saveas dialog, the dialog disappears and the the workbook doesn't save.
A form loads and the first thing the user is to do is enter a specific date in a text box (preferably in, "mm/dd/yyyy" format.)
Is there any way to set up the text box so that when the form loads, the, "/"'s (slashes) are already in place? And can I set it up so that if the user enters in the date in any other manner aside from, "mm/dd/yyyy", that it will give them an error?
Finally, I want the user's date input to store on a worksheet (in row, "G".) So the first time the user uses the form, they type in a date and it stores the date in the FIRST AVAILABLE CELL IN ROW, "G." (example: "G1") The next time they use the form and type in a date, it is to store in, "G2", then in, "G3", ect. Each time they fill out the form, the new entry is to save in the next row beneath the previous entry.....
For other text boxes and combo boxes I have been using the following code to do this:
Sheets("Sheet1").Range("A" & intRow) = Text.Value (or Combobox.Value - depending on whether it was a text box or a combo box.)
Unfortunately I have NO idea how to apply this logic in regards to text boxes that are to store dates.
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
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.
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).
I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)
I have text in column X and dates in column A...
How can I return the last (latest) date (column A) that "hold" was used (in column X)?
Working with project information which is about 1,000 project. I have two worksheets one has the start date/finish dates for each phase of a project -Define, Measure, I, Analyze, Control. Depending on the date I need it to be read and insert a value in my summary worksheet. The summary sheet contains the 12 months. I need the formula to read two columns start/finish, if the month is the same as the header in the second report return a letter of D, M, I, A, C if not leave blank.
View 9 Replies View RelatedI don't why the following code is not working. I am trying to force a date format when a user is trying to enter a date in anycell
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsDate(Target.Value) Then
Application.EnableEvents = False
new_date = Format(Target.Value, "dd/mm/yyyy")
Target.Value = new_date
'MsgBox new_date
Application.EnableEvents = True
End If
End Sub
I'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)
I'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)
I have a text file I am importing and using text-to-colums.
Some of the text is "+TOL" "-TOL" and when excel converts to columns it is forcing the text to a formula "=+Tol" "=-Tol" giving me #NAME? in those cells.
I can format the cells as text, which is fine for now since I'm using VBA, but if I need to use any formulas then I'm SOL...
So I have a Workbook that I want to make read-only/protect after a certain date. I also want to force the user to have macros enabled. So far I have the following script, but despite changing it to read-only it doesn't seem to do anything. I have also tried to enable protection on the sheets after that date, but am getting errors.
View 2 Replies View RelatedThe code below first asks a user to input a weekending date (which must be a Saturday). The value of the input box goes to cell C1 of my spreadsheet. That part works fine. I also have a function in Cell G1 with the function:
=TEXT(C1, "dddd")
I don't know if this is the best way to test for a Saturday but it is what I have,
Where is goes bad is if the date is not a Saturday. I have a loop to force a new date install via a input box but it won't update C1 with the inputed data to recheck for the Saturday value. Below is the whole code I am working with...
Dim aa As String
Dim bb As String
If Range("C1") = "" Then
Do While bb = ""
bb = InputBox("Please Enter a Weekending Date!")
Loop
Range("C1").Value = bb
End If
If Range("G1") "Saturday" Then
Do While aa "Saturday"
aa = InputBox("Weekending Must Be a Saturday. Please Enter a New Weekending Date")
Loop
Range("C1").Value = aa
End If
To ensure correct data entry, I need to ensure that the filled cells in a column are always in Uppercase for example, irrespective of whether they are typed in upper or lower case.
I have found the following code, which seems to work quite well, even though I have the impression that it slowed my workbook down slightly.
Is it possible to make a spreadsheet so that everything that is typed into it is in CAPITALS? I have a need for that for a spreadsheet that I am using at work, but I remembered that in the title box above it only capitlaizes the first letter. I thought if it can do it with the first letter can Excel do it with all of the letters.
View 3 Replies View RelatedI need to ensure that all text entered into columns G,J,K and L of a specific spreadsheet are all UPPER CASE.
Someone pointed me to this link VBA TO CONVERT TEXT TO UPPERCASE which does it for the full sheet but I need column specific. I am using Excel 2010
I have many shortcut words to make full name using Auto Correct option under Proofing
I am using this code to make force range in Uppercase
[Code] ....
But when i put a auto correction value in small case "ip" {like ( IP = IRFAN PAT )}
Then the cell value is going in uppercase "IP" but auto correction is not working
I want ..if i type in range "ip" result shoul be (" IRFAN PAT ") not "IP"
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?
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.
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?
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.
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.
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 Relateda2 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.
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 RelatedA 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.
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 RelatedI 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
