i have the text as string data in column A and it is just a text not date format. i want to convert this using formula text function to get result shown in column B and C. is this doable?
Excel 2010ABC1DateRevised DateRevised Date 2Fri 4 Feb 2011Friday, February 04, 20112/4/20113Fri 5 Oct 20074Fri 28 Apr 20065Fri 30 Sep 20056Fri 23 May 20087Fri 3 Feb 20068Fri 30 Sep 20119Fri 11 May 200710Tue 1 Jan 201311Fri 13 Aug 201012Tue 25 Jun 201313Fri 8 Jun 201214Fri 25 Mar 201115Fri 12 Feb 201016Fri 5 Sep 200817Fri 6 Nov 200918Fri 6 Mar 200919Fri 21 Aug 200920Fri 6 Jan 201221Fri 5 Jan 200722Fri 15 Jan 201023Fri 12 Jun 200924Fri 7 Jul 200625Fri 21 Oct 201126Fri 31 Oct 2008Sheet4
I have userform with date pickers and have text boxes overlaid on these, when I select todays date from the date picker it does not display the current date in the text box (I have 8 date pickers on the userform). If I select another date then reselect the current date it works. It has occasionally worked but why.
Below is the code for populating the text box from the Date Picker.
Private Sub DTPicker1_Change() TextBox1.Value = DTPicker1.Value End Sub
The initialize userform code uses the following to format and set the textbox
I have a spreadsheet where we will be inserting the Arabic Hijri date taken from a Customs document (bayan). On my spreadsheet I'd like to have a column immediately next to the Hijri date that would convert the Hijri date to the Gregorian date so both dates will be visible for our Arabic and Western personnel who will be using the spreadsheet.
I have a userform which requires the user to enter the date a training course was completed. Initially I used DatePicker as I was running Excel 2010 and had that working, however the workbook I am creating will be used on about a dozen machines, all of which have different versions of Excel. To avoid needing calendar Add-ins to be downloaded and installed for this feature I decided to go the vba created calendar route. I am using one I found on this forum: [URL] .....
It works well for me using the example spreadsheet provided in the post but I am having some difficulty incorporating it into my code. I believe I have imported the appropriate forms and modules because I can get the date populate to the Label Box on my userform but when I try to put the Date into the spreadsheet along with other data such as name and course duration, the name and course duration populate but the date cell remains blank and execution completes without error.
I have tried playing around with the data type thinking I had some issue using the .Value attribute with a String but .Text gives me an error as does converting from String to Number format.
I will attach the workbook but for a quick look, the portion of the code I believe that is not working is in here:
I have a fairly large database in excel 2010 that in being updated everyday by a employee. In column A there are a set of dates all in the same format 9/1/2011 what i would like to happen is that if there are any dates in column A then it cut out the row and paste it into Sheet1 (the data is stored in sheet2).
I have two excel 2010 files that I want to run this on. Each one has about 10+ worksheets in them. I am looking to have a vba script that will look at columns D and E starting at row 4 and check if they are expiring in the next month or have already expired (before today's date). Then it would return a text file that will say which worksheet it is on, the row and column, and what date is in that cell.
I came across this excellent code here on this site but it does the opposite of what I need. This code deletes empty or blank rows if the cell in column I is blank. I actually need to delete the row if the cell in column I has a date or any data. Using Excel 2010.
Sub deleteBlankRows() On Error Resume Next Columns("I").SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub
I need to put a number as a text. It is 14 digits long (35071245130000). If I change it from a number to a text, it re-formats to a scientific number (3.50712E+6).
I can not use the ' in front of the number because it makes all of my vlookups mess up.
The reason I need this changed is because I have a several databases/excel spreadsheets that are being linked through Access. The number is stored as text in the other databases. Because of this, the spreadsheet with the number is not recognizing the number version and the text version as the same.
How to make excel show this full number when formatted as text?
I have Excel 2010. I do not remember having this problem with other versions at my previous employer.
I am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
Excel Version : MS Excel 2010 Attachment filename : <Forum to plot the values.xls>
My requirement is i want to plot the values V1, V2, V3 and V4 from the given condition (A1 / A2 / A3 / A4) and the given date.
If the given date is not available then the formula should take the older date than it not next date. For example if the given date is 10-Mar-14 and the date is not available for the given condition then it should take the older / earlier dates like 09-Mar-14 or 08-Mar-14 like and it should not take 11-Mar-14, 12-Mar-14.
What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.
E.g. A1 = "AA" A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"
I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried
Using Office 2010, I am trying to do a averageifs formula for a specified date range. I can get it working by specifying the date range in the formula itself, however when "pointing" the formula to a specific cell with a date in it, the formula gives me a div/0 error.
Formula that works is: =AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"08/29/2012")
What I need is a version of: (currently not working) =AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"H1")
The date range will constantly change as I want it to show me the past 30 days only.
I'm using Excel 2010. I have three columns. Column A contains the date, Column B contains the time, and Column C contains the date and time. Column C is the result of a formula, which adds Columns A and B (ie. =A2+B2). So I have the following...
Date Time Date and Time
1/15/13 9:00:00 AM 1/15/13 9:00:00 AM
1/15/13 9:00:05 AM 1/15/13 9:00:05 AM
I'm using a defined name to store a date and time, which I'm going to use as a lookup value in a Match function. I've defined MyVal as follows...
1/15/13 9:00:20 AM
Excel automatically converts this to the following decimal number...
When I first run either of the following two macros, they correctly return 5...
Code: Sub test1() Dim x As Double x = [MyVal]
Debug.Print Application.Match(x, Range("C2:C10"), 0) End Sub
Sub test2() Dim x As Date
x = [MyVal] Debug.Print Application.Match(CDbl(x), Range("C2:C10"), 0) End Sub
However, when I save and close the workbook, and then re-open it, these macros both return an error. Is this some sort of bug in Excel?
I have got a table with data from 2005 to date, (for example) For the purpose of what I am doing I need a column which shows date ranges between September 2005 - August 2006 to show as 2005/2006, then September 2006 - August 2007 to show as 2006/2007. I have done some research and seen that, Potentially, a nested 'IF' can be used but it can only be used 7 times which would cause a problem going forward....
Is this the only way or is there a better way (without using VB)?
I have been struggling to find where my code is throwing up an error 1004 on the last line of the below code. I have a number of tables which will update automatically from Pivot tables on another sheet.
So the first part of the code is adding in the new column and then I want to autofill the date into the headers of the column which I thought the below would do, but I just don't understand why I keep getting the error. My data is dynamic as it will grow month by month which is why I am using R1C1 referencing.
I have a Column of dates in this format: 5/19/2014 8:08:44 AM I am trying to get this to read 20140519 or yyyymmdd It doesn't matter what format I change to, it will not update unless I activate the cell and then tab or enter out of it. I have 3000 rows to convert. with this either by setting or VB Sub?