I have imported date field values from ClearQuest(IBM Product) into Excel worksheet. after import the data display format in the excel looks like
Thursday, February 19, 2009 9:11:42 AM
Now I need to calculate the age of the defect in number of days by using the formula
=NOW()-J26 where the cell J26 contains the above data . However I always get the display as #VALUE in the cell.
I changed the field format for cell J26 to custom dddd, mmmm dd, yyyy h:mm:ss AM/PM but still does not work.
My question now is- how do I convert dddd, mmmm dd, yyyy h:mm:ss AM/PM to mm/dd/yyyy h:mm:ss AM/PM so that I would be able to find the age of the defects?
Or is the a way to change the format for NOW() to something dddd, mmmm dd, yyyy h:mm:ss AM/PM?
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!
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'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?
How do I change a date "10/01/2004" into a format like 20041001? I used concatenate(right(A1,4),left(A1,2),mid(A1,4,2)) but the function takes the serial value of the date as an argument and concatenates that . So I get a different value.How can I get away with it?
I am having some issues converting the dates in column A on the attached spreadsheet to the mmmm format. I cannot get the dates to change format at all so I was hoping someone might have some ideas why. I have tried everything I can think of. The only thing that has worked so far has been to retype the date in the cells. I have 32000 rows of data I have to convert.
I 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?
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!
I'm trying to convert a date in the following format: 19970121 to 1/21/1997 and am having trouble getting rid of the leading zero in the final version. I also need it to pick up the 2 digits in months that have them from 9 (Sept) through 12 (Dec), though, for use in a VLOOKUP function. Can this be done at all?
Here is the code I've been using so far that doesn't entirely work for what I need it to do:
When broken into individual cells, dates before the 13th of the month are recognised as "American" dates and display as "08/10/2005", "08/11/2005", 08/12/2005" etc. with automatic date format. Dates from the 13th onwards are left in general format as "13/08/2005", "14/08/2005".
I have written a macro that converts each date individually to its correct Excel serial number, but I can't help thinking that there must be a simpler way, and that I cannot be the first to have asked this!
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.
I have several large excel spreadsheets with thousands of entries (autocad drawing numbers). In one column it lists the date the drawing was created. Unfortunatley most of the entries were input as text. Even though I have formated the column to a date format (01/01/2009) most entries are not converting. Below is an example of some of the cells:
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.
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.
I use software that exports date/time stamps in the format of: MM/DD/YYYY hh:mm:ss.s I like to keep the data in this general format so that we can keep it consistent and it can be useful to others using other software. I recently had to do some date/time stamp manipulations where I added 61 days to a dataset because the data was erroneously set to a diferent calendar. (Still don't know how that happened but it isn't relevent to this issue) When I set up a formula to add 61 days, it appears that it automatically converted it to the excel serial date/time format before adding the 61 days. Now I have a column of data that is mostly general format but has this portion in serial format. How can I get the serial format changed back to general so that the entire column is in the same format?
