I have two cells. Cell A1 contains ‘The date is ’ cell B1 is formatted in the date format, 2007-01-24. I need to concatenate these two cells so that on cell C1 I have ‘The date is 2007-01-24’ but whatever I try excel keeps on changing the date to a text value and hence I am left with: ‘The date is ‘This is the date'39141 or this is the date 39141. how to get to the result that I am after?
View 5 RepliesI 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'm having a problem getting the date to show up when I add the date as a referenced cell to a formula using CONCATENATE. The date shows up as a serial value instead of a date format. The formula that I'm trying to get to work is in cell J7 on the attached file.
View 3 Replies View RelatedI have a template that works from Excel, but is saved in a .csv by default because .csv is the only compatible file format with the database we use. I have one column that I need to be in a "text" format, and another column that must be in a number format with no decimal place approximations (whole numbers). I know how to go in and format these columns myself, but it is clear that .csv does not like it. Every time I open the file, it resets the formatting in my columns. So, I have to re-format these columns every time I open or re-open the file. If there's information in this file, I have to re-format my columns and re-enter my information so that it is formatted correctly. For example, my first column must be text because it will have things like 001,002,003 etc. I tried using a custom format ###, which did not work. If I save the file and leave it alone, it preserves the format when I import into the database. If I have to re-open it to add something or make changes, all of my first column info is now standard numbers like 1,2,3 etc. So, I reformat the columns, and I have to re-enter 001,002,003 etc. This gets annoying after awhile, especially if my sequences (numbers) go into the triple digits.
Obviously, there's no easy way around this due to the features of a .csv file. So, I would like to add code that executes on file start-up (or open). I want this code to format my columns as stated before, one column must contain text format, another has numerical format with whole number approximation.
I have several cells which I usually need to copy and paste. These cells contain single characters with a different format than the rest, as shown here:
inventar
not just with one word, but several words within a cell. The problem is that when I try to paste a part of a cell having multiple formats, it always pastes the text in uniform format, disregarding the format of those letters having a different format.
Does anyone know of a way to paste text having multiple formats when it consists of a part of a cell's content and not the full cell?
I have a couple Excel files that exhibit a very odd and annoying behavior. When I save the files, everything is fine and all cells are formatted as they should be (e.g., general, text, numbers, etc). However, when I open the file again, all cells are formatted as date. This is quite annoying since some underlying data will be destroyed.
I already checked if the format style "normal" is damaged, and indeed it was (the standard format was set to date). However, I changed this back to general before saving, yet still see all cells formatted as date after reopening the file (this does not happen consistently... most of the time everything is fine but occasionally I get the date mess-up).
I have an application that generate an excel sheet for me. In this sheet I have multiple rows with duplicated dates and text. With VBA I need to clear the content of all the cells that contain duplicated date and keep just the first row.
View 3 Replies View RelatedI can select an individual cell or range, select Format->Cells->" date" format, hit okay, and nothing actually changes unless I click on a cell so that it brings up the edit cursor. I can then hit enter (not changing the content of the cell in any way) and then Excel recognizes it as a date. The cell values are all in the standard "3/14/2001" format, with the exception that single digits are 0-padded, and there are no spaces preceding or following the date string. The issue here is that without the cells being recognized as actual dates, they cannot be properly sorted, eg: the original range:
01/04/2005
01/01/2005
01/02/2004
01/03/2005
is sorted, incorrectly, to:
01/01/2005
01/02/2004
01/03/2005
01/04/2005
...obviously for ascending order, the single 2004 date should be placed at top. Any ideas other than a custom date-text parsing macro? I could do that, but the size of each sheet, the number of sheets per batch, and the frequency of individual batches makes that idea completely impractical. ...Technically, my macro couldn't sort the above ranges correctly, but on further investigation, I noticed I couldn't do it manually either.
I have a spread sheet with a date colume that reads: 2012-06-27-19 I need to have this read like 06/27/2012 but nothing I do is working I have tried to go to the formatting process and backing the hr:mm out and that doesn't work. I really don't want to go line by line to manually correct this issue.. HENCE ... over 2000 lines
Second question: If I have a column that reads 02/15/2012 and another column that reads 3/27/2012 how to a format a 3rd column to make it read total number of days between 1st date and 2nd date?
I have two questions regarding date format and hope you can provide input.
1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?
2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?
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'm working with a big dataset and the dates come out in this way:
94WAO7t.png
EDIT: Example of book included
I have a list of dates stored via the following :
20100101 how can I convert this to date/time format to show 01/01/2010?
I'm using nested SUBSTITUTE formula to make some changes to the some of the data. I'm also using IFERROR to return the input if it does not find the criteria I have specified. I have lot of variations in my input data.
The formula works fine for all except for date format input.
Sample:
N90232Y09--->SUBSTITUTE(A1,"N","")-->90232Y09
12335--->12335
12/3/1923---->58936 (I want the date to be retained)
why the date format is changed even if don't specify any changes for it. I expect my IFERROR to just retain the input as it is.
I have a program that exports the date as 20120621 (4 digit year, 2 digit month, 2 digit day). I have tried =datevalue(cell) but it returns #value!. I have tried =today()-(cell) and it also returns #value!. Is there a formula or something I can do to covert this field to a date?
View 3 Replies View RelatedI have a couple of columns, once edited need to update a table in SQL. This is my code
[Code] .....
However it is not taking UK date formats, it is assuming it is an american format, I get conversion of varchar data type to datetime results in an out-of-range value. What should I add to change the format? The format is in UK on the spreadsheet seemingly....
I'm in the military and for maintenance we use the Julian date the format goes as follows "09357" that is simplified as 09 = the year and 357= the three hundred and fifty seventh day of the year. so 09357= 12,23,2009..
so on to my question:
how can i make excel auto convert the julian date to the regular date and vice versa?
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)
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??
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.
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
Hello, we have a stop light spreadsheet for status with several different projects. In each row there are conditional format status green - good, yellow - needs work, red - bad...based on data from another tab.
There is an "overall status" column that pulls the worst color that exists for a given row.
=IF(COUNTIF(F1:V1,"r")>0,"R",IF(COUNTIF(F1:V1,"y")>0,"Y",IF(COUNTIF(F1:V1,"g")>0,"G","")))
There is also a date for each row. Now we want to pull the worst color in the "overall status" column for a date range...and display that on a separate summary tab.
For 4/1/09 thru 4/15/09 - pull the worst color from the "overall status" column from that date range.
I am trying to format certain cells to change colors depending on the date and the content of the next field to show due dates.
cell label
due date
date completed
c5
au5
be5
c6
au6
be6
c7
au7
be7
c8
au8
be8
What I need is for the contents of au5 to be green up until 10 days before the due date, yellow up until 5 days, and red with less than 5 days until the due date. If the date completed is entered into be5, then the contents of au5 should be black.
Is this possible, and if it is, how do I set this up? I tried to use
=$AU5-TODAY()>=10
And choose green formatting (something I found on yahoo answers), but when I enter the date the text is still the original black font.
I am pasting a large amount of data from a sql server query into excel. There is multiple tables output in each query. Some of the data is date/time and excel is formatting all of these cells to time. The date/time cells to not neatly line up in any row or column, so I cannot just format any give column or row. I need a macro that will find all of the cells that are formatted as time and change them to a date format.
View 7 Replies View RelatedI have a dataset where the dates are in the following format:
19970803 = 3rd August 1997 (YYYYMMDD)
My aim is to sort the data into months and into 'turn of the month' periods.
-Is there a way to isolate rows using a search term like "****08**" for all the august entries?
-Likewise if i want data between 27th-3rd for every month is there a way to sort for entries between "******27 - *******03".
My macro asks for a user input in DD-MM-YY format (which is the same format of all cells in the Excel worksheet).
I then instruct the macro to paste the string into cell A1 in 'TEST' sheet.
However when it pastes in the format is MM-DD-YY with the user input DD being the worksheet MM etc.
For example 06-04-14 becomes 04-06-14.
My code is below:
[Code] ......
Attached File : TEST.xlsm‎
I have the code below working as I want it to, when data is input in column 2 the date and time is automatically input into the corresponding cell 3 columns to the right. I want to ensure that the date is changed to the UK format dd-mmm-yy. I have formatted the column to be in that format but for some reason it has suddenly started changing the date to US format. Can this code be changed to force the date to be in UK format?
View 3 Replies View RelatedI am having trouble with formating a to date. I have ~5,000 lines of data that are formatted as follows: the last 6 digits refer to date. For example, below line should be date 09/24/09. PRIVATEBANK 3.65%09PRIVATE 3.65%09 PRIVATEBK&TR 3.65 092409. I am using the right function to isolate the date. In cell A2:
View 2 Replies View RelatedI have a worksheet where in a column, dates are stored in various date format i.e. it may be DMY or MDY or YMD.
08-06-12
08-06-12
08-07-12
08-07-12
13/08/2012
13/08/2012
13/08/2012
14/08/2012
Above is just for an example, above dates are for the month of August, but as can been some cells are in MDY formate and some in DMY. Can I have formula to convert different date formats into one date format?
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