I'm trying to use the Date Time Picker control as a time picker and only want hours and minutes to show. Can this be done with its custom format property (Example)? The usual number formatting string doesn't seem to work.
I'm building a userform excel application and am using the MS date/time picker control. I've run into the familiar "computer doesn't have OCX" problem and want to avoid using a custom OCX.
I was thinking of just making my own control instead. Has anyone made one in VBA? I found some VB examples which I could port over, but they use a lot of api calls. For reliability, I'd like to keep this as simple as possible though.
I have a lot of data in the following format: 20080531T085930. Formatting the cells with yyyymmddThhmmss doesn't seem to be working. What's the correct way to handle this?
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?
On the attachment i have a userform which pops up when cell B2 is selected. It is a month view from the additional controls on the control toolbox. I've also added a couple of text boxes with spin buttons.
A few amendments i want to make to it are:
User form pops up next cell B2
'Enter button' enters date and time into cell B2
Spin buttons increase/decrease hour field in incremnts of 1 and minutes field in increments of 5
I'm trying to use the Date/Time ActiveX control in Excel 2000 and it is giving me problems. I am attempting to use this control contained directly on a worksheet (not contained on a separate form). My problem is that each time I start the workbook, the date/time control resizes itself to have an extremely large height. I set the control's height at 18 but upon loading it shows the height as about 5x that value. However, the properties of the control still show it listed with a height of 18? No matter what I do, I cannot seem to find a way to have the control maintain its initial height. I've tried this in several different workbooks, and it does the same everytime.
How should i reformat the date and time picker? at this moments, it shows the date in the format of mm/dd/yyyy but the format that i want is dd/mm/yyyy.
I am trying to use the Date and Time Picker 6.0 and I have add it to the userform and named it DateofContact.
When I play the userform I get a error stating:
"Compile Error-Method or Data member not found"
Below I have added the code and it shows in red where the problem is.
Private Sub UserForm_Activate()
PSA.Text = Cells(lCurrentRow, 2).Value cboTypeofContact.Text = Cells(lCurrentRow, 3).Value DateofContact.Text = Cells(lCurrentRow, 4).Value TimeofContact.Text = Cells(lCurrentRow, 5).Value End Sub I am not sure what to put in its place.
I recently reformatted my hard drive and reinstalled my software. Now I don't seem to have the Date and Time Picker Control (among others) that I used to.
I did a thourough install of Microsoft Office 2000 Professional, and switched from Windows 98SE to Windows 2000 Professional. Isn't that control standard in such an installation? If its not, where can I get it?
i have just discovered the Microsoft date and time picker. at the moment i have a text box on my userform into which i put the date.can i use code to transfer the date to the text box or do i use the date picker instead of the textbox. if i used the date picker what is the code for the date picker
I have some excel tools developed in Excel 2003 which uses the date and time picker control (DtPicker). When I open my files in Excel 2007 the DtPicker is missing and the change_event will not load the calendar. I cannot find and activate the datepicker in the Reference base.
I just installed Office 2013 Pro. In my Office 2010 Additional Controls toolbox, the control toolbox has a Date and Time Picker(DTPicker1) . I cannot find it in the Additional Controls toolbox in Excel 2013. I certainly hope they did not choose to not make it available in Excel 2013 - this is an extremely important control for my Excel spreadsheets as it allows inputting a date range on a userform with DTPicker1 and DTPicker2 and generating reports using VBA in conjunction with Autofiltering code on a Generate Report button.
I inserted the Date & Time picker Control into my worksheet. I have it linked to a cell. When I change the date, the new date goes into the cell, but it does not appear to be a "date" . The date formulas that use that cell do not evaluate properly. It looks different than when I type a date directly in to the cell (it is left justified). I tried formatting the cell as a date and the format property of the control is set to shortDate.
I want a drop down calender to choose date from and found that i can use More Controls> "microsoft Date and Time Picker 6.0 (SP4)" for excel 2007 but it doesn't work with other excel and shows a "x" not recognised in some other machines.
I am using the Microsoft Date and Time Picker Control 6.0 (SP4) from the more controls option on the developer Ribbon. The issue I have is when I protect the sheet I lose the functionality?
I have ranges selected as editable by user when the protect is on - this includes the linked cell to the date picker but it still doesn't work.
I have just added two date and time picker controls to my spread sheet to allow the user to select the date range.
They are linked to cells C9 & C10.
The problem is that the formula I using to check whether the first selection pre-dates the second has stopped calculating correctly when using the date and time pricker controls.
The formula is:
=IF(C9>C10,"Error","OK")
I have checked it on the same sheet by just typing the dates in and it works fine...I have tried formatting cells C9 & C10 to short date, general, text, number but it still does not calculate correctly.
I am trying to add a calendar picker to Excel 2010. Everything I read online says that it is possible, you just need to use the ActiveX control "Microsoft Date and Time Picker Control 6.0", and that it should be listed under Developer Tab -> Insert -> More Controls ->... But it's not listed.
I also tried downloading the Microsoft Windows Common Controls-2 6.0, but when I tried to add it to Excel using "Register Custom...", it says "Cannot register this control".
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)
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 loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).
i have the required date as part of a string, which i need to pull out and then format it as "yyyy-mm-dd hh:mm:ss" and put it in another file which will be saved as .csv
Here is the code i am trying to modify without much success so far
Sub dateFormat()
Dim dtMydate As Date Dim sMydate As String Dim strWhen As String
strWhen = "As on 13-MAR-2007 14:45:59 Hours IST" ' the starting string 'strWhen = Cells. Find("As on")
'take out the date portion from strWhen strWhen = Mid(strWhen, InStr(strWhen, "-") - 2, (InStr(strWhen, "Hours") - 1) - (InStr(strWhen, "-") - 2))
dtMydate = CDate(strWhen) Debug.Print dtMydate 'returned value is 13/03/2007 2:45:59 PM
dtMydate = Format(dtMydate, "yyyy-mm-dd hh:mm:ss") Debug.Print dtMydate 'returned value is again 13/03/2007 2:45:59 PM...
I am needing to create a sequential serial number using a format yyyy-nnn, where yyyy is the current year and nnn is a sequential number (2007-001). I've tried using both a formula and custom formats but cannot get the cell contents to display as desired. I've used TODAY()&"-"&"001", which gives a valid result of 39100-001, but this not what I need. I've tried many other combinations in the custom option for formatting the cell.
I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
I have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells): 2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758 2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698 2005/11/03 00:07:13.830 2005/11/03 00:10:14.971