date - time parsing code

cpearson directed me to working code to correctly parse the time and date
formats.. it works great when only either date or time is used as a
worksheet_change. How can I add a second worksheet_change for the cells that
have date?  (the time cells already have the procedure)
Thanks chuck... wonderful site


0
mgmombo (10)
5/26/2005 2:36:22 AM
excel 39879 articles. 2 followers. Follow

1 Replies
397 Views

Similar Articles

[PageSpeed] 52

You only get one worksheet_change event per worksheet.

But you could do different things if you can define when times or dates should
be applied.

For instance, if the dates go in column A and the times go in column B, you
could set up the code that way.

Kind of...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Me.Range("a:a")) Is Nothing Then
        'process as a date (column A)
    Else
        If Not Intersect(Target, Me.Range("b:b")) Is Nothing Then
            'process as a time (column B)
        Else
            'do nothing
        End If
    End If
    
End Sub



But if your dates are intermixed (randomly) in the same column, you'd have to
something different--maybe add an indicator character that says to treat this
entry as a time or treat it as a date.



mgm wrote:
> 
> cpearson directed me to working code to correctly parse the time and date
> formats.. it works great when only either date or time is used as a
> worksheet_change. How can I add a second worksheet_change for the cells that
> have date?  (the time cells already have the procedure)
> Thanks chuck... wonderful site

-- 

Dave Peterson
0
ec357201 (5290)
5/26/2005 3:07:32 AM
Reply:

Similar Artilces:

Date formats
Hi. A text box bound to a DateField has yyyy\.mm\.dd as Format. Data is displayed correctly but while entering it (apparently) reverts to the computer date format, messing everybody's mind. Is there a way to keep entering and showing date format to yyyy\.mm \.dd? Thanks Henry You need to use an input mask. They are described in Acccess Help system. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "H. Martins" wrote: > Hi. > > A text box bound to a DateField has yyyy\.mm\.d...

Last Balanced Date
Does anyone know a quick way to get a list of accounts and the last date they were balanced? I'm finding myself having to select each account and manually check to see if it is up to date. I would think there would be a report of some kind that would display this somewhere, but I can't seem to find one. Any suggestions? Thanks in advance...Skip Skip, I've been thinking of the same exact question ever since I made the upgrade from Money 2004 to 2005. The ability to see the last balanced date across all accounts did exist in the 2004 version, and it was very useful. It...

Data from .txt file being read in as date, and should be text
I have a macro that reads in data from a .txt file. The data contains user IDs that consist of the first 4 letters of their name and 4 numbers. When names are read in that begin with MARCxxxx (ie MARC1234), excel is formating them as a date, and I lose the original data (even when I try to format them after the import, they are a number and the letters are not there. I've tried to format the column that they data read into before the import, but excel still imports them as a date. How can I make the import be read as text? Here is the code for the section I am reading in data for. Do Whi...

help populating appointment times in table
I will try to explain as simply as I can. I have a database that keeps client records and creates appointment dates and times after update of a provider name combobox by running a macro with several queries. Now here is where I don't know what I am doing. I have it working perfectly providing someone has not used the 9:00 spot in the appointment book because I have it defaulting to 9am. How do I add code to have the default change to a different time for any record where the 9:00 spot is already used? I am not very good with the code writing though I can manage to add a snippet in...

Web query time out problem
Hi, I have a workbook with around 350 sheets each with a web query in. If try to update all, about a half of the sheets will successfully updat before the message 'www..... The access to this internet site ha exceeded the allotted time'. Can anyone suggest a workaround? For example would it be possible t update the web queries in batches of 100? Thanks, Berti -- claytor ----------------------------------------------------------------------- claytorm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1161 View this thread: http://www.excelforum.com/show...

Daylight Savings Time Issue
I have an Exchange 2003 installation with 930+ mailboxes. I understand that I have to apply a patch to my Windows 2003 server and the Exahgne Patch on that server. I also know that I have to apply the patch for Windows XP. My Quesitons are: 1. Do I have to use the The TIme Zone Data Update Tool for Microsoft Office Outlook IF I use the Exchange Calendar Update Tool? 2. Will there be problems if I applied both patches to my server that also is the host for my Exchange installation and my clients are not updated or if they are updated. I just don't want to do double work if I don...

Jon Peltier's code: can't delete initial series??
I wrote this on my machine running Win XP and XL2002. It worked. I gave it to a friend running Win2000 and XL2000, and it fails on the code to delete the initial series. What else can we do? Ed Sub MakeNewScatterChart() Dim wkb As Workbook Dim wks As Worksheet Dim LastRow As Long Dim LastCol As Long Dim colData As Long Dim cht As Chart Dim chtObj As ChartObject Dim rngData As Range Dim rngXval As Range Dim rngSer As Range Dim strTitle As String ' Get limits of data Set wkb = ActiveWorkbook Set wks = wkb.Sheets(1) LastRow = wks.Range("A65536").End(xlUp).Row LastCol = wks....

date formatting changes
I have a VB macro that changes all date formats to dd/mm/yy. This works fine. However, sometimes when I go back into my files at a later date, I find that the dates convert back to dd/mm/yyyy. Why does this happen when I have specifically set the format to dd/mm/yy? There are some custom date formats that don't belong to you. I'm betting that you happened to use the same format that excel uses to tell it to pick up the format from the windows regional settings (under control panel). xl2002 is more honest with the way it deals with dates. (I think it's new with xl2002, but ma...

opening unopened workbbok via code
Hi, I posted this question a while ago, but the responses I got wer unfortunately beyond my comprehension as a self-taught VBA'er! My problem was that I wanted to check if a file was open and if no open it before working on it. I developed the following code which seems to work OK. On error Goto Line1 Windows("a").Activate Goto Line2 Line 1: Workbooks.Open Filename:="C:\A.xls" Line2: ..... The problem I have is that if I need to check on more than one workboo in one sub (eg "B.xls"), I get an error message "subscript out o range" for the line, W...

sequential date related question
Hi - If for example within cell A3 the date of 09/03/2001 was entered would it be possible in the cell beneath it, A4, to write code tha would break the date down into parts (e.g., Month, Date and Year) an to modify these parts individually? Therefore within cell A4 the code would suggest that it equaled A3 bu that it would have the potentail to modify its parts (e.g., Month, Dat and Year) and not as a whole (e.g., A3+365) Thus what I would like to place in cell A4 is code that would b somehow similar to the following =IF(MONTH(A3)<>12,DATE(YEAR(A3),MONTH(A3)+1,1),DATE(YEAR(A3)+1,1,1))...

Exchange 2000 Keeps Sending the same Mail each time the Server is Restarted.... !
Hi Folks; I'm running Exchange 2000 and I recently sent out email to 5,600 people that belong to my website. The problem is, each time I re-start the server it sends out all those emails - again. I've re-started the server 4 times now over the last few weeks (due to Windows Updates etc) and each time it sends the same emails to the same 5,600 people. Needless to say, people are starting to wonder about me ;-) Does anyone know what would cause Exchange 2000 to do this? Thanks! Marvin Hi, Look in the SMTP queue folder to see if the message is saved there. Leif "Marvin Mill...

Date and time stamp
I would like to place a time and date in a cell that is only update when the cell that it is linked to is updated in any way. I have trie today() and now() but that updates anytime I do anything anywhere o the spreadsheet. I am using this as a log and as such I want to keep "time stamp" when someone enters their initials. Thanks, Troosh:confused -- Message posted from http://www.ExcelForum.com For a possible solution see http://www.mcgimpsey.com/excel/timestamp.html -- For everyone's benefit keep the discussion in the newsgroup. Regards, Peo Sjoblom "troosh >&...

custom date format mmm/yy
Hi, is there any way in a Report to customize the date displayed in the report to be mmm/yy? Or even mmm/yyyy would be better for me than the options Access seems to offer in their reports. All I see there is Short Date, Medium Date, Long date - they all take up so much space in a densely packed report. Thanks, Harold You can use your own formats In a control's format property, enter mmm/yy or mmm/yyyy or mm/yy For a discusson of the formatting characters, put your cursor in the format property and press the F1 key for help. -- John Spencer Access MVP 2002-2005, 2007 Cent...

OLEobject properties at run time
Good afternon, my question is, how can I change the OLEobject properties of a textbox control at run time? any suggestions? ActiveSheet.OLEObjects("textbox1").Object.BackColor = &H8080FF is one way. User wrote: > > Good afternon, my question is, how can I change the OLEobject properties of > a textbox control at run time? > > any suggestions? -- Dave Peterson Thanks for responding, in my case I need to change the property "EnterKeyBehavior" to true, how make it? Besides, how make for viewing the diferent methods and properties that it ex...

filtering or sorting by sender time zone
I would like to optimize my opportunities for rapid two-way dialog by email. For example, when I read my email in Boston at 7:00 AM, I first respond to email from India, who are nearing the end of their day, then from Europe, who are about 6 hours ahead of me, and so on. Currently, I do this just by looking for specific senders and handling their email first. But, it would be great if I could write a filter that would sort email into GMT + 5.5, GMT+2, GMT, GMT-5, and GMT -8. Is this possible? Thanks, Rob ...

Date in Report/Query
Hi. I have a report that has worked up until this past week. The query behind the report has the following expression: DateAuth: Format([CredAuthDate],"mm/dd/yy") If I type in 12/31/09....report works fine. Type in anything with "10" as the year and it fails. Any suggestions would be greatly appreciated. If I take off the format for the field it works. Is something different for the year 2010? This is an Access 2003 database. Thanks... I suggest applying criteria to an 'unformatted' field and turn off the display by unchecking the 'Sh...

How can I get yesterdays date in a custom footer &[DATE]-1 does .
How can I get yesterdays date in a custom footer &[DATE]-1 does not work try =today()-1 chuck <chuck@discussions.microsoft.com> wrote in message news:5A51D4C3-07A5-48A8-A594-09F51D4B46A6@microsoft.com... > How can I get yesterdays date in a custom footer &[DATE]-1 does not work Chuck - You will have to manually or programmatically change the displayed date in the footer. There are no page setup codes that will put a different date in a footer. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...

Add chart
Hi, Can anybody find what is wrong with this code? It will just make a empty chartobject. I believe the ranges are ok, it probably is some bad syntax or object reference... The code should make a chart with two series of xlColumnClustered and one serie of xlLineMarkers. Thank you /tskogstrom --------------------------------------- CODE: Sub UppdateChartCF() Dim cht As Chart Set cht = Sheet1.ChartObjects("R_CF").Chart On Error Resume Next '(if no chartobject) Sheet1.ChartObjects("R_CF").Delete On Error GoTo EndCode 'Left and Top location = named ranges With Shee...

Date trasfer when importing emails to CRM
When I import old emails into CRM from Outlook they are tagged in CRM with the today's date, the day I imported them. Is there any way to alter this date once in CRM to reflect the actual date of the email? I believe your referring to promoting emails within Outlook. If so, please see this thread: news:5DF7B2F7-B5CE-4FF9-80E3-421D4FE5F500@microsoft.com -- Jason -- This posting is provided "AS IS" with no warranties, and confers no rights. "Eric" <Eric@discussions.microsoft.com> wrote in message news:6CF94DF6-7659-4793-AF6E-439BB480DC29@microsoft.com......

PO Last Receipt Date
If an item has been received on a PO, when you select the PO in Purchase Order Entry, you can go into the Purchasing Item Detail Entry window and see a Last Receipt date for the item that has been received. Is there any way to print this date on the PO printout? Elaine, Easily. This field is, not suprisingly, "Last Receipt Date" and it is the PO Line table. If it isn't already on your report, you should be able to drag it onto the report as long as it's not one of the temp table based reports. And if it is, you may then be able to link the po line table in and th...

Timesheet to monitor flexi-time
Hi All, Does anyone have a spreadsheet that can be used to record employee timesheet details, capable of accommodating simple flexi-time and part time hours. Ideally, i would like an option to add employees and then enter their agreed working hours (this is to be used as a look up). Then, On a weekly basis, data will be entered for the previous 7 days� Hours worked can fall under the following categories... Hours worked �normal� time Hours worked flexi time Hours �Sick� Holiday Bank Holiday Authorised Absence Paid Authorised absence unpaid I'd also like A way of viewing information...

Figuring sql server version via code
Hi Is it possible to check in .net; 1. If sql server 2008 express is installed on a machine? 2. The name of sql server 2008 express instance or instances? 3. If a particular sql server 2008 express instance contains a specific database? Many Thanks Regards You can use several methods to enumerate SQL Server instances. Here are a couple: http://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx http://www.sqldbatips.com/showarticle.asp?ID=45 To determine whether a specific instance contains a certain database, you can connect to the instance's master databa...

Report codes for Matrix Parent
I'm preparing for an upgrade from QS2000 and would like to know if th following is possible. I'm testing the demo version of RMS an imported my database. I'm a shoe and apparel store and matrixes ar very important to us. My reseller has recommended the Retail Real Fashion bundle, but I would like to try something else in th meantime.. I've modified a Detailed Item Sales report and would like to ad columns for the following items associated with the Matrix Paren Item Matrix Lookup Cod Matrix Descriptio Matrix Dimension 1 (I want to see the value associated with Chil item I...

how can i select all the cells with same color on a sheet if there are multipale colors by vba code
how can i select all the cells with same color on a sheet if there are multipale colors by vba code Much better than the 1st post. try this Sub selectyellows() For Each cel In ActiveSheet.UsedRange If cel.Interior.ColorIndex = 6 Then mystr = mystr & cel.Address & "," End If Next cel mystr = Left(mystr, Len(mystr) - 1) Range(mystr).Select End Sub -- Don Guillett SalesAid Software donaldb@281.com "uobt" <uobt@aol.com> wrote in message news:1103129633.880542.286200@f14g2000cwb.googlegroups.com... > how can i select all the cells with same color on a shee...

Bizarre Excel Copy/Paste Date Issue
I'm working on 2 xls Excel 2000 files (with all Office Updates and Windows Updates applied on this WinXP PC). I'm copying a date from a cell in A.XLS and pasting it into B.XLS. Both cells are formatted as dates. I copy the 1/1/1999 from A.XLS but when I paste it into B.XLS, it shows up as 1/1/1994. There are NO formulas involved and I have auto-calculation ON. They are not text, they are true date entries. I've never seen anything like this and wouldn't know where to look for help on this except for here. Any suggestions greatly appreciated. -Rob Rob, One work...