Format Date In Cell As Text
Feb 2, 2008
I am pulling reports out of a non-windows based program. This program spits out the data directly into an Excel spreadsheet, with but a click of a button. I imagine that the designers of this program assumed that this would be useful, but it's actually proving to be quite irritating.
The Date format they have chosen for their program is NOT the date format that Excel uses, but it resembles it enough to cause serious problems. The original program tracks dates in a "dd/mm/yy" format, rather then Excel's "mm/dd/yy". To my VAST personal irritation Excel does NOT convert this into Text (which could be
manipulated by snipping it into the correct format) but instead converts it into it's own Date format, leaving me with a useless and incorrect serial number as the only data.
I cannot interact with the exporting process at all, and as the program exports DIRECTLY into Excel I cannot manipulate the data prior to import (which would be one relatively easy way of avoiding this problem). I am left trying to figure out a way to reconstruct an actual date out of this mess. Is there any way to convert a date serial number BACK into text?
View 2 Replies
ADVERTISEMENT
Jan 10, 2007
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
View 9 Replies
View Related
Oct 6, 2008
Example......
In D2 I have: =B2
In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
View 3 Replies
View Related
Jan 17, 2012
I have these as input entered as text:
January 01, 2011 January 01, 2011 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010 May 12, 2011 June 02, 2010 February 28, 2010
but I need it in this DATE format:
MM/DD/YYYY
I need it as a formula to be entered into a cell in excel (not VBA)
View 9 Replies
View Related
Nov 8, 2006
Date:31-Oct-06 (the date is in cell A3)
Formula: "as at"& " "&A3
Result: as at 39021
I need the results to be "as at 31-Oct-06". Is there a formula for this??
View 2 Replies
View Related
Dec 12, 2007
I've created a group of user forms used for recording jobs and displaying details of jobs and have come up against a problem I think you may be able to help me with.
When the user creates a new record, I've written some code to stamp txtdate text box with the current system date and this information is then copied onto a sheet called Data into a specific column.
This part works fine and as the column is formatted to handle dates it displays fine.
However, when I pull the data back in, it displays the date as the serial number date, not formatted as dd/mm/yy. I've tried every fix I can find to sort this but none of them seem to work.
The code I use to pull the data in is as follows:
ComboBox1.BoundColumn = 11
txtdate.Value = ComboBox1.Value
The idea is that the user selects a job number from combobox1 and then the appropriate column is pulled into each txt box on the form.
At the same time, I'm trying to do the same for the system time as well. Unsurprisingly I have a very similar problem here and use the same code as above. I've tried formatting both date and time txt boxes in the userform, the data transfer code etc etc with no success.
View 3 Replies
View Related
May 8, 2008
Is there a way to make a cell display today's date as YYYYMMDD?
Assuming data!a2= 7989, I need the following formula to display as shown below
="txt"&data!a2&"txt"&today
displayed as
txt7989txt20080507
View 4 Replies
View Related
Mar 22, 2013
I have attached excel, where date is mention in B Column but when I am formatting it, it is not showing in proper date format. I want format in eg 18-Mar-2013.
Book1.xlsx
View 14 Replies
View Related
Oct 27, 2008
I have dates formatted as dd/mm/yyyy and I need just the year, but with either text or standard format. (so no easy yyyy solution which would work)
I just seem to be too stupid, can't copy and paste as value, as that doesn't change a bit. If I format it as text it turns up sth like 34875 ofc.
View 4 Replies
View Related
May 1, 2009
I have been sent an excel sheet, that contains a text column with a date and time format below
24/04/2009 1700
15/05/2009 2359
16/04/2009 1400
Is it possible to convert this into date & time to use in a calculation.
View 2 Replies
View Related
Feb 12, 2010
I am trying to display both text and a date in the same line. Currently I have something like ="The Date Is " & A1. where A1 is a cell with a date formatted in a preferred format. However, once I call reference to the cell, excel re-formats it into the serial date, so it displays something like. The Date Is 40220
instead of something like: The Date is 2/11/2010. Does anyone know how to format the number back into a date form? Just going through the format menus and setting it to date does not change it.
View 2 Replies
View Related
Feb 12, 2014
I have a userform with a number of text and combo boxes. One textbox is called ("txtDate").
I would like the ability to type in "1-10" and have the textbox display "Friday, January 10, 2014".
There is also a command button that enters the data to a sheet in the workbook.
View 5 Replies
View Related
Jun 9, 2014
i am looking for vba macro to convert date format (indonesian language) to be text format, see this below :
sample (col.a)
after vba
09/10/14
09 Oktober 2014
26/12/11
26 Desember 2011
05/06/10
05 Juni 2010
etc...
View 6 Replies
View Related
Nov 13, 2006
I am trying to make a very simple macro but get stuck in de Excel date format. When using the date "=TODAY()" in one cel it is no problem but I want to integrate it in the CONCATENATE function.
I want to create this text "(WG 01-01-2006)" but then with the actual date of that day of course, which can be inserted in de active cell.
Whatever I do, copy, pastespecial, reed text with RIGHT, change format on an temporary cell etc. Excel keeps returning the number in Excel date format counting from 01-01-1900. Nice for calculating but I want the date text!
View 9 Replies
View Related
Nov 27, 2007
I've got some data from a unix database that I have been able to import into Excel 2003. I want to use this data in an Access 2003 database but the dates are formatted all wrong. Date comes out like this "FEB08" in Excel.
I want to convert this into a date format that I can use as an actual date when I import this data into Access. I was thinking about doing the conversion in Excel before importing it into Access. If it would be easier to import into Access and then convert
View 9 Replies
View Related
Jan 14, 2008
I have run a report from one of our databases and it gives me the date in the following format :
Apr 26, 1983 12:00 AM
When i sort it does it alphabetically - i think the cell is actaully text (general) format - i can't seem to change it to any of the inbuilt date foramts - i would like to sort my spreadhseet by date !
View 9 Replies
View Related
Oct 16, 2007
Is there a way to convert date to text of format (number stored as text) either directly using excel formulas or VB macros. We've a requirement to pass today's date as a string in the format of "number stored as text". For this, i've in cell A1 a formula today() which gives today's date 10/15/2007 and I'm looking an output of '20071015 in cell A2 and this should be treated as text with a symbol " ' " before 20071015.
View 2 Replies
View Related
Feb 21, 2014
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
View 4 Replies
View Related
Aug 3, 2012
I have a calendar userform that enters a date into a userform textbox in the format mm/dd/yy.
I am in the UK, so default date format is dd/mm/yyyy but i need it as above - mm/dd/yy
So, when I get a MsgBox to repeat Textbox1.Value (the date from the calendar object, formatted mm/dd/yy) and repeat it in format dddd mmmm dd yyyy, it reads the value not as I want it, mm/dd/yy, but as the usual dd/mm/yyyy - so instead of reading 08/01/12 as Wednesday August 01 2012 it is giving me Sunday January 08 2012 - even though in the cells on the worksheet that are populated from the textbox, the date reads correctly, so 08/01/2012 is indeed 1st August 2012 not January 8th 2012...
So it's the MsgBOx function reading the textbox in the userform wrong, and formatting makes no difference...
Can I fix this without changing my regional settings? Is there a way to set the region in vba then unset when leaving the program? I don't want to have to change regional, and anyway, the program will be used by others who won't know / want to do that.
View 5 Replies
View Related
Jun 18, 2014
So I'm just a lowly intern who has been given the task of changing date formats in a bunch of spreadsheets. The string is as follows:
1015 Dixon Schwabl Est. for Creative Svcs 101207.pdf
I have to change the date at the end (mmddyy) so it reads as yyyymmdd. There is a long column of these strings, and i have to do it on over 300 spreadsheeys.
View 3 Replies
View Related
Sep 10, 2009
What I have is a double quoted, comma delimited text file that is created from an external application, every now and again we need to open the file to make corrections and this is easier if we open it via Excel using the delimiter options, the problem is when saving the file as a csv it removes the double quotes, however I have found some vba coding that adds the double quotes back, which works great, but it changes the format of the date fields from what the original file had from 09-Aug-2009 to 09/08/2009.
I suspect this may be because the file is opened before the double quotes are added and excel changes the format.
Is there any code I can add that will stop the date format from changing ...
View 12 Replies
View Related
Nov 8, 2012
I have a column with the following values:
SubmitDate
November 8, 2012 1:37:07 PM GMT+05:30
November 8, 2012 9:32:03 AM GMT+05:30
November 8, 2012 4:32:54 AM GMT+05:30
November 7, 2012 10:28:11 PM GMT+05:30
November 7, 2012 8:36:43 PM GMT+05:30
November 7, 2012 6:33:13 AM GMT+05:30
[code]......
I need another column, with just the month specified there based on above dates, so that I can manipulate the complete sheet further using a pivot. Some formatting issue isn't allowing me to use month() function, to derive the month of the date mentioned in the cell.
Using Auto-filter is too tedious, since I'd have to do it 12 times to cover each of the month. Tried recording a macro, but that too isn't supporting much, since it gets specific to cells and hence isn't re-usable in another sheet with similar data.
How can I automate the task of extracting just the month from the above column?
View 4 Replies
View Related
Jul 21, 2009
i'm trying to convert a column (P) from date dd-mmm-yy, subtract 5 days, and paste value as YYMM using a macro. I've borrowed bits from other macros and producted this but it's not working (and i've played around with it to the point it doesn't make sense anymore!)
Option Explicit
Sub ADD_REPORT_DATE()
Dim LR As Long, i As Long
Dim tempValue1
Dim tempValue2 As Date
Worksheets("Report").Select
LR = Range("P" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
Set ActiveCell.Value = Text(ActiveCell.Value - 5, "YYMM")
Next i
End Sub
View 9 Replies
View Related
Jun 6, 2006
I want to format my spreadsheet title so it reads number of reports for june 2006
="number of reports for MM YYYY"
So far all i get is -
="number of reports for 6 2006"
using my code which is
="number of reports for "&$BD$2&" 2006"
View 6 Replies
View Related
Sep 5, 2007
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.
View 9 Replies
View Related
May 10, 2014
I'm copying data from another source to excel, and for certain date entries they come as text in the following format "On 14 May at 8:00". Is there anyway to convert multiple entries like this into a standard date format? With or without time is fine - just the date will do.
View 4 Replies
View Related
Jan 23, 2010
Please refer to attached spreadsheet
I receive data from an external source and it displays what appears to be dates in column B.
In column G I apply a formula to display month/year info.
It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require.
I have attached just a small example.
I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.
View 6 Replies
View Related
Dec 24, 2012
I have a question about conversion of a text format into date format.
2 computers, the first windows vista, the second Windows 8, both Excel 2007.
The purpose is to calculate the difference between 2 dates with hour:minutes:seconds, like for example:
18/12/2012 19:20:00 and 18/12/2012 19:23:03.
So in this example the result should be 00:03:03.
Both dates are formatted with 'personalized' : dd/mm/yyyy hh/mm.
In the first computer everything works like it should, but in the second it is apparently impossible to transform the first of those date h:m:s which is originally in a text format into a date format. I tried all the methods which i found to transform a text date format into a date , like multiplication with 1, Datevalue and others, but nothing seems to work, the result is always "#Value".
View 4 Replies
View Related
Dec 15, 2009
I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail.
It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this.
View 9 Replies
View Related
Aug 1, 2008
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.
View 5 Replies
View Related
