I have an Excel report that I import from IBM Clear Quest tool (Web App). A field, 'Submit_date' in this report has data in the following format:
'Jan 12, 2012 12:00:00 AM'
If I double-click in the cell the cursor is after the AM. When I click outside of the cell, the date time stamp changes to 1/12/2012.
My question is, how to change the entire column so that all data gets converted to short date (mm/dd/yyyy) and thereby making it a data sortable field.
I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...
January 1 2012 to January 31 2012
The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.
I want to convert Hex to Binary. I want the Binary in 8 different cells.The hex input would be lets say A1 I would want to convert that to eight different cells Bit7 G10, Bit6 G12, Bit5 G14, Bit4 G16, Bit3 E10, Bit2 E12, Bit1 E14 and Bit0 E16.
Then I want another Hex input on A2 and convert that to eight diferent cells.
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 have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
I have a column in my data set that consists of dates and times in this format: 2014/08/02 01:46:49 PM. I am trying to convert these dates and times to numbers so that I can actually use these values for calculations and regression analysis. When I click on one of the cells, I get a number that is revealed along with decimals. For example, the cell with 2014/08/02 01:46:49 PM had a stored value of 41853.574. I tried to highlight the entire column of dates + times and click on the format cells button. I selected the number category under number but that did not work. I also tried to use the =DateValue(Cell #) function but that did not allow for distinguishing between the same date but different times.
What are my options for converting these dates and times to numbers that I can work with? Is there a way to get the entire column of dates + times to show each cell's numeric stored value?
Problem: The macro will be assigned to a command button and will be used by laypersons when they finish filling in data on a worksheet in Workbook 1. The sheet contains maybe 30 columns and 50 rows with a mix of fixed values and values generated by Vlookup and Indirect formulas. I need to copy the sheet from Workbook 1 to Workbook 2. Workbook 2 will be for archival purposes so I want to convert all formulas to fixed values. The catch is dealing with some cells that contain hyperlinks to PDF files...
Current Solution: I currently do this with a macro that moves/copies the sheet from Workbook 1 to Workbook 2, it then selects all cells in the new sheet in Workbook 2, copies all cells, then pastes-special "as values" to the exact same cell locations. This works great for me since the cell formatting and data in the sheet are VERY irregular and I have merged cells all over the place. This method keeps the exact formatting I need maintain:
The problem is that a few of the cells have hyperlinks with "friendly names" and I lose the hyperlinks when I convert to values. The hyperlinks are not inserted directly, they are created by a formula, =HYPERLINK("N:Filepath"&C16&".PDF", "Click_For_PDF"), and the row and column that contains the hyper link will vary for each sheet I want to migrate from Workbook 1 to Workbook 2 using this macro. I want to keep the hyperlink active with the clickable friendly name in Workbook 2.
Possible Solution: I'm open to all types of solutions, but is there a way to essentially use my existing macro but AFTER converting to values with paste-special, go back to the original sheet in Workbook 1 that still contains formulas (or maybe a temporary duplicate sheet I migrate to Workbook 2?), search for all cells with a "value" of "Click_For_PDF", copy ONLY those cells and paste (normal) into the corresponding cell locations in the sheet in Workbook 2 that now contains fixed values? ALL of my hyperlinks have the friendly name "Click_For_PDF" so it should be an easy way to identify the hyperlink cells. The cell location of the hyperlink copied in Workbook 1 needs to carry over to Workbook 2 and I said before, the row and col vary with every sheet I want to archive with this macro.
I saw the =WEEKNUM function introduced to Dave196. I tried it but I do not get the desired result. I get "# NAME?" What can I be doing wrong? Also, how can I have Excel take a date and convert it to a number? I want to convert a date into an invoice number (eg. 4/5/07 ===> 20070405)
As the title says I was wanting to convert a date into an alphanumeric number.
After searching around for a long time the closest thread I could find was this one : [URL] ....
I would like to enter the date by dd/mm/yyyy and have it come out to an alphanumeric number.
The alphanumeric number for example would be C21501 Where "C" is the year starting from 2013. So in this case C would be 2015
Next two digits would be the week of that year. So 21 would be in the month of May
The following number would be the day of that week starting from Monday. So in this case 5 would be Friday.
The last two numbers would be the consecutive number for that day. For example two orders could be placed that same day so the first would be C21501 and the next would be C21502.
The following function reads many worksheets in one workbook and put the information into one worksheet. F22 to Q22 is dates in the format of mmm-yy on the many worksheets. On the one worksheet B1, C1, D1, etc... is dates also formatted mmm-yy. Now the many sheets only have a 12 month period but could be any date within 60 months. In the many worksheets the beginning date in F22 is based on the date that is entered into G8 of the many worksheets. The function is:
Code: Function HrsByMonth(strField As String, MonthNum As Integer) As Long Dim sht As Worksheet, i As Long, j As Integer HrsByMonth = 0
I have a spreadsheet that I use to convert a purchase order ship date from the actual date to the corresponding week it falls out on. The fiscal year always starts on February 1 regardless of the day of the week. The problem i am encountering is when the year changes. As soon as I enter 01/01/2010, the response I get is -4, where as 12/31/2009 is 48.
I am using the following formula that I found somewhere, where R2 = 02/01/2009 (02/01/2009 falls out on a Sunday). =INT((R2-DATE(YEAR(R2),2,1)-WEEKDAY(R2,1))/7)+2. I need to make the formula "not care about" the day of the week.
Down loading data I receive dates as a number that indicates YYMMDD (ex. 130603 is 03 JUN 13). Is there an easy way to convert these number strings to the actual date indicated other than typing it in?