Dates:
------
November 1, 2008
1 novembre 2008
In the formula bar, these formated dates appear as 11/1/2008.
What I need to do is copy the formated dates and paste them into another column so that they show up as November 1, 2008 or 1 novembre 2008 and not as 11/1/2008.
Does anyone know of a non-macro formula or function that can do this? Is there a "paste as is" function?
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.
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 RelatedI am facing a problem with number formats. I have downloaded some data from a database. In the data, there are some date columns in MM/DD/YYYY format. In a seperate workbook, I have copied that data as values.
Now when I am trying to change the formats of the dates to d-mmm-yy, they don't change. The formats are not not changing to any other format. I even tried changing the formats using VBA, but it did not work.
What I have done is create a macro in Excel and then opened up the VBA editor to make a lot of changes to the initial macro. I am reformatting a whole lot of separate spreadsheets over many workbooks.
Here is the code that is generating the error (Run-Time error '438', Object doesn't support this property or method):
Code:
Range("E2").Select
Selection.Format = "long date"
With Selection
.Font.Size = 11
' .Format = LongDate
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
As you can see, I first tried the ".Format = LongDate" and did put LongDate in "" which did not work, then I tried the "Selection.Format = ..." after the "Range" statement, and that did not work either. So you will actually know, I am trying to set the format for the cell "E2" to a Long Date format.
I have a problem when i try to format a cell. i download data from a website and it gives me the date and time in the first column, ex. 1/1/2009 5:00:00 pm. when i format the cell to date, the time is erased and the date remains which is what i want, ex. 1/1/2009. however, when i click on that cell or any other in that column, the display above, or "insert function" line still show the contents of the cell with the date and the time, ex. 1/1/2009 5:00:00 pm. is there any way i can change that so the time is erased and only the date remains, ex. 1/1/2009 when i click on the cell?
I am facing a problem with number formats. I have downloaded some data from a database. In the data, there are some date columns in MM/DD/YYYY format. In a seperate workbook, I have copied that data as values. Now when I am trying to change the formats of the dates to d-mmm-yy, they don't change. The formats are not not changing to any other format. I even tried changing the formats using VBA, but it did not work.
View 2 Replies View RelatedI 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?
I'm trying to copy the value of one column to another, column A has date and time in this format: 6/1/2013 6:35:00 AM and I'm trying to get ONLY the dates to another column so that when I do my pivot, I can sort it out by date. I tried =A1 and I can format the column to how I want it but the value of the cell still remains in date&time. Is there any formula I can use? or should I change anything in my pivot table?
View 4 Replies View Relatedjust wondering if its possible to format a cell to display date and week number and if so how to go about it
eg
04/01/09 week 1
I have two cells. both formatted to contain dates.
cell a1 will have a date in it. cell a2 will have a date calculated to be cell a1 + 42 days.
now, when cell a2 reaches a date that is greater than cell a1 + 42 i want cell a2 to be formatted as fill red.
so, i have tried a conditional format as =a1+42 >=TODAY() but it always highlights as red even if the value in cell a2 is less than cell a1+42.
I have a cell that is constantly being changed from a date to a number. Excel automatically formats the cell as a date when a date is entered but if I re-enter a number it leaves the format as a date.
Is there anyway to automatically change the format back to general if a date is not entered.
I had this bit of code obviously it dosent work.
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("E8:E46")) Is Nothing Then
If IsDate(Target.Value) Then
Target.NumberFormat = "d/mm/yy;@"
Else
Target.NumberFormat = "General"
End If
End If
Cancel = True
I'll get straight to the point:
where ( Date > 9/20/2007)
The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).
I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".
I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.
Not very good at this but I have the basic sheet attached. All I want is a way to highlight the first cell when the date is within 30 days of the "Due date".
Calibration Record-example.xls
In cell B5 of sheet1 contains a date which changes everytime when i open excel file. The date is in the M/D/YYYY format.
I am using the following code to use that date in MM/DD/YYYY format.
[Code] .....
If cell B5 of sheet1 is 2/1/2014, but my code is resulting in wrong as 01/02/2014.
Right, sorry this is probably a simple one but I can't do it... I have information in a variable that is "1-4" the variable is defined as a String but whenever I use the following code excel turns it into a date.
View 2 Replies View RelatedI download & import CSV files/ sheets from bank, visa accts etc & I am trying to automate the processes I use including ensuring I (& others) will use the same naming conventions eg visa "yymm" = V0701, Direct Cedits = DC0701 etc. I am having trouble renaming or ensuring the active sheet is named according to the date in A3 eg in the example I want the sheet to be renamed V0702 as the date in the cell is 02/02/07 "dd/mm/yy" format ( imperial system). In BOLD is what I have & the renaming is messy becuase I searched the forum, Walkenbach's bible & help menu.
Sub VisaMonthlyAcct()
Dim shtName As String
Dim newName As String
ActiveSheet.Activate
'ActiveSheet.Name = shtName
newName = "V" & Format(DateValue("A3"), "yymm")
'Old name for future reference = Visa0701 (2)
ActiveSheet.Name = newName
Worksheets(shtName).Name = "V" & Format(Range("A3"), yymm)
'Worksheets("Visa0701 (2)").Name = "V" & (FormatDateTime(Range("A3"), yymm))
'To select Date column & format
Range("A3:A66").Selection.NumberFormat = "dd".............
One of my userform text boxes is for the input of a date. this date is being exported to a defined cell in excel, but in a US format, mm/dd/yyyy. i need this to be exported into excel in UK/Aussie format dd/mm/yyyy as our accounting software is getting confused (i am also).
Private Sub TextBox3_Change()
Range("C7").Value = TextBox3.Value
End Sub
I have created a standard input box for the date (UK)
datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date)
Sheet1. Range("e" & x).Value = datereceived
I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),
How can I condition format a cell to show no format if the cell contains a date
Please see the attached book1.xlsx for more info. Book1.xlsx
I get one report.xls workbook by FTP. This book contain date and time in cell A14 in sheet1. e.g. 30-04-2006 19:38:00
I need to have macro to extract only time and paste it to cell A56 in same sheet. How can I do that. The date format is 30-04-2006 19:38:00 and it may be 05/05/2006 7:15 am. Or it could be any type of date format. Please send me how can I work with different date and time format to extract the only time and paste to other cell.
I have created a userform but I am having extensive problems with the date formats.
My system is set to UK and short date is set to: DD/MM/YYYY
When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format.
So I finally figured out to use DateValue to format it correctly for example: ...
I would like to highlight (conditional format) the "response required by" (Row A) cells / dates if the "current date" (cell $B$2) is exceeded AND if the "response provided By" (Row B) remains BLANK:
Current Date
=Today()
Response Required By
Response Provided By
29-10-2013
23-10-2013
[Code] .......
One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
View 5 Replies View RelatedEvery month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.
For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.
What I need is: Search in the A column, if date format leave it , if not change from general to date format.
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
View 9 Replies View RelatedI have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:
=IF(LEN(B218)=10,(MID(B218,4,2)&"/"&LEFT(B218,2)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=9,(MID(B218,3,2)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=8,(MID(B218,3,1)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,"")))
Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:
12/10/2013
12/9/2013
9/11/2013
9/9/2013
7/25/2013