Automatically up date time in a cell

Hi all,
I can enter the time in a cell by using the =now() and can update it by 
hitting the F9 key but how can I make it change automatically to always 
remain the same as the computer clock.  I would imagine I need a macro to do 
it ...can someone please help me.

Thanks....Mark
0
mark564 (750)
5/11/2005 6:50:02 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
345 Views

Similar Articles

[PageSpeed] 19

check this thread....
http://www.excelforum.com/showthread.php?t=364995

Mangesh



"Mark" <Mark@discussions.microsoft.com> wrote in message
news:A8EC77F7-14D8-4E3A-B683-004E549D1037@microsoft.com...
> Hi all,
> I can enter the time in a cell by using the =now() and can update it by
> hitting the F9 key but how can I make it change automatically to always
> remain the same as the computer clock.  I would imagine I need a macro to
do
> it ...can someone please help me.
>
> Thanks....Mark


0
5/11/2005 8:48:59 AM
Mark,

here is one way but it is not simple

Add the code below to the modules indicated, and start the clock by running
the following code


  Set timer = Range("A1")
  StartClock


To stop the closck, just run the StopClock macro.


'-----------------------------�------------------------------�--------------
-- 
In one code module add this code




Option Explicit


Private Declare Function FindWindow Lib "user32" _
    Alias "FindWindowA" _
   (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

Private Declare Function SetTimer Lib "user32" _
   (ByVal hWnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "user32" _
   (ByVal hWnd As Long, _
    ByVal nIDEvent As Long) As Long


Public timer As Range

Private WindowsTimer As Long


Public Function cbkRoutine(ByVal Window_hWnd As Long, _
                           ByVal WindowsMessage As Long, _
                           ByVal EventID As Long, _
                           ByVal SystemTime As Long) As Long
Dim CurrentTime As String
    On Error Resume Next
    timer.Value = Format(Now, "Long Time")
End Function


Sub StartClock()
    timer.Value = Format(Time, "Long Time")
    fncWindowsTimer 1000, WindowsTimer        '1 sec
End Sub


Sub StopClock()
    fncStopWindowsTimer
End Sub


Sub RestartClock()
    fncWindowsTimer 1000, WindowsTimer        '1 sec
End Sub


Public Function fncWindowsTimer(TimeInterval As Long, WindowsTimer As Long)
As Boolean
    WindowsTimer = 0
    'if Excel2000 or above use the built-in AddressOf operator to
    'get a pointer to the callback function
    If Val(Application.Version) > 8 Then
        WindowsTimer = SetTimer(hWnd:=FindWindow("XLM�AIN",
Application.Caption), _
                                nIDEvent:=0, _
                                uElapse:=TimeInterval, _
                                lpTimerFunc:=AddrOf_Callback_Routine)
    Else 'use K.Getz & M.Kaplan function to get a pointer
        WindowsTimer = SetTimer(hWnd:=FindWindow("XLM�AIN",
Application.Caption), _
                                nIDEvent:=0, _
                                uElapse:=TimeInterval, _
                                lpTimerFunc:=AddrOf("cbkRoutin�e"))
    End If


    fncWindowsTimer = CBool(WindowsTimer)

    DoEvents

End Function


Public Function fncStopWindowsTimer()
    KillTimer hWnd:=FindWindow("XLMAIN", Application.Caption), _
              nIDEvent:=0 'WindowsTimer
End Function

'-----------------------------�------------------------------�--------------
--


'In another code module add this code


Option Explicit

Private Declare Function GetCurrentVbaProject Lib "vba332.dll" _
    Alias "EbGetExecutingProj" _
   (hProject As Long) As Long

Private Declare Function GetFuncID Lib "vba332.dll" _
    Alias "TipGetFunctionId" _
   (ByVal hProject As Long, _
    ByVal strFunctionName As String, _
    ByRef strFunctionID As String) As Long

Private Declare Function GetAddr Lib "vba332.dll" _
    Alias "TipGetLpfnOfFunctionId" _
   (ByVal hProject As Long, _
    ByVal strFunctionID As String, _
    ByRef lpfnAddressOf As Long) As Long


'-----------------------------�------------------------------�--------------
--
Public Function AddrOf(CallbackFunctionName As String) As Long
'-----------------------------�------------------------------�--------------
--
'AddressOf operator emulator for Office97 VBA
'Authors: Ken Getz and Michael Kaplan
'-----------------------------�------------------------------�--------------
--
Dim aResult As Long
Dim CurrentVBProject As Long
Dim strFunctionID As String
Dim AddressOfFunction As Long
Dim UnicodeFunctionName As String


    'convert the name of the function to Unicode system
    UnicodeFunctionName = StrConv(CallbackFunctionName, vbUnicode)

    'if the current VBProjects exists...
    If Not GetCurrentVbaProject(CurrentVB�Project) = 0 Then
        '...get the function ID of the callback function, based on its
        'unicode-converted name, to ensure that it exists
         aResult = GetFuncID(hProject:=CurrentVBP�roject, _
                             strFunctionName:=UnicodeFuncti�onName, _
                             strFunctionID:=strFunctionID)
        'if the function exists indeed ...
        If aResult = 0 Then
            '...get a pointer to the callback function based on
            'the strFunctionID argument of the GetFuncID function
             aResult = GetAddr(hProject:=CurrentVBPro�ject, _
                               strFunctionID:=strFunctionID, _
                               lpfnAddressOf:=AddressOfFuncti�on)
            'if we've got the pointer pass it to the result of the function
            If aResult = 0 Then
                AddrOf = AddressOfFunction
            End If

       End If

   End If

End Function


'-----------------------------�------------------------------�--------------
--
Public Function AddrOf_Callback_Routine() As Long
'-----------------------------�------------------------------�--------------
--
'Office97 VBE does not recognise the AddressOf operator;
'however, it does not raise a compile-error ...
'-----------------------------�------------------------------�--------------
--
    AddrOf_Callback_Routine = vbaPass(AddressOf cbkRoutine)
End Function


'-----------------------------�------------------------------�--------------
--
Private Function vbaPass(AddressOfFunction As Long) As Long
'-----------------------------�------------------------------�--------------
--
    vbaPass = AddressOfFunction
End Function








-- 
 HTH

Bob Phillips

"Mark" <Mark@discussions.microsoft.com> wrote in message
news:A8EC77F7-14D8-4E3A-B683-004E549D1037@microsoft.com...
> Hi all,
> I can enter the time in a cell by using the =now() and can update it by
> hitting the F9 key but how can I make it change automatically to always
> remain the same as the computer clock.  I would imagine I need a macro to
do
> it ...can someone please help me.
>
> Thanks....Mark


0
phillips1 (803)
5/11/2005 8:50:43 AM
and one more...

http://www.mvps.org/dmcritchie/excel/datetime.htm

- Mangesh



"Mark" <Mark@discussions.microsoft.com> wrote in message
news:A8EC77F7-14D8-4E3A-B683-004E549D1037@microsoft.com...
> Hi all,
> I can enter the time in a cell by using the =now() and can update it by
> hitting the F9 key but how can I make it change automatically to always
> remain the same as the computer clock.  I would imagine I need a macro to
do
> it ...can someone please help me.
>
> Thanks....Mark


0
5/11/2005 8:53:24 AM
Bob,

This is a great piece of code - except that I can get it to start, but I 
can't get it to stop! No error messages are displayed, but the clock just 
keeps on runnin' !

I'm running Excel 9.0.4402 SR1 on Windows 2000 5.00.2195 SP3

Any ideas?

Pete

"Bob Phillips" wrote:

> Mark,
> 
> here is one way but it is not simple
> 
> Add the code below to the modules indicated, and start the clock by running
> the following code
> 
> 
>   Set timer = Range("A1")
>   StartClock
> 
> 
> To stop the closck, just run the StopClock macro.
> 
> 
> '-----------------------------­------------------------------­--------------
> -- 
> In one code module add this code
> 
> 
> 
> 
> Option Explicit
> 
> 
> Private Declare Function FindWindow Lib "user32" _
>     Alias "FindWindowA" _
>    (ByVal lpClassName As String, _
>     ByVal lpWindowName As String) As Long
> 
> Private Declare Function SetTimer Lib "user32" _
>    (ByVal hWnd As Long, _
>     ByVal nIDEvent As Long, _
>     ByVal uElapse As Long, _
>     ByVal lpTimerFunc As Long) As Long
> 
> Private Declare Function KillTimer Lib "user32" _
>    (ByVal hWnd As Long, _
>     ByVal nIDEvent As Long) As Long
> 
> 
> Public timer As Range
> 
> Private WindowsTimer As Long
> 
> 
> Public Function cbkRoutine(ByVal Window_hWnd As Long, _
>                            ByVal WindowsMessage As Long, _
>                            ByVal EventID As Long, _
>                            ByVal SystemTime As Long) As Long
> Dim CurrentTime As String
>     On Error Resume Next
>     timer.Value = Format(Now, "Long Time")
> End Function
> 
> 
> Sub StartClock()
>     timer.Value = Format(Time, "Long Time")
>     fncWindowsTimer 1000, WindowsTimer        '1 sec
> End Sub
> 
> 
> Sub StopClock()
>     fncStopWindowsTimer
> End Sub
> 
> 
> Sub RestartClock()
>     fncWindowsTimer 1000, WindowsTimer        '1 sec
> End Sub
> 
> 
> Public Function fncWindowsTimer(TimeInterval As Long, WindowsTimer As Long)
> As Boolean
>     WindowsTimer = 0
>     'if Excel2000 or above use the built-in AddressOf operator to
>     'get a pointer to the callback function
>     If Val(Application.Version) > 8 Then
>         WindowsTimer = SetTimer(hWnd:=FindWindow("XLM­AIN",
> Application.Caption), _
>                                 nIDEvent:=0, _
>                                 uElapse:=TimeInterval, _
>                                 lpTimerFunc:=AddrOf_Callback_Routine)
>     Else 'use K.Getz & M.Kaplan function to get a pointer
>         WindowsTimer = SetTimer(hWnd:=FindWindow("XLM­AIN",
> Application.Caption), _
>                                 nIDEvent:=0, _
>                                 uElapse:=TimeInterval, _
>                                 lpTimerFunc:=AddrOf("cbkRoutin­e"))
>     End If
> 
> 
>     fncWindowsTimer = CBool(WindowsTimer)
> 
>     DoEvents
> 
> End Function
> 
> 
> Public Function fncStopWindowsTimer()
>     KillTimer hWnd:=FindWindow("XLMAIN", Application.Caption), _
>               nIDEvent:=0 'WindowsTimer
> End Function
> 
> '-----------------------------­------------------------------­--------------
> --
> 
> 
> 'In another code module add this code
> 
> 
> Option Explicit
> 
> Private Declare Function GetCurrentVbaProject Lib "vba332.dll" _
>     Alias "EbGetExecutingProj" _
>    (hProject As Long) As Long
> 
> Private Declare Function GetFuncID Lib "vba332.dll" _
>     Alias "TipGetFunctionId" _
>    (ByVal hProject As Long, _
>     ByVal strFunctionName As String, _
>     ByRef strFunctionID As String) As Long
> 
> Private Declare Function GetAddr Lib "vba332.dll" _
>     Alias "TipGetLpfnOfFunctionId" _
>    (ByVal hProject As Long, _
>     ByVal strFunctionID As String, _
>     ByRef lpfnAddressOf As Long) As Long
> 
> 
> '-----------------------------­------------------------------­--------------
> --
> Public Function AddrOf(CallbackFunctionName As String) As Long
> '-----------------------------­------------------------------­--------------
> --
> 'AddressOf operator emulator for Office97 VBA
> 'Authors: Ken Getz and Michael Kaplan
> '-----------------------------­------------------------------­--------------
> --
> Dim aResult As Long
> Dim CurrentVBProject As Long
> Dim strFunctionID As String
> Dim AddressOfFunction As Long
> Dim UnicodeFunctionName As String
> 
> 
>     'convert the name of the function to Unicode system
>     UnicodeFunctionName = StrConv(CallbackFunctionName, vbUnicode)
> 
>     'if the current VBProjects exists...
>     If Not GetCurrentVbaProject(CurrentVB­Project) = 0 Then
>         '...get the function ID of the callback function, based on its
>         'unicode-converted name, to ensure that it exists
>          aResult = GetFuncID(hProject:=CurrentVBP­roject, _
>                              strFunctionName:=UnicodeFuncti­onName, _
>                              strFunctionID:=strFunctionID)
>         'if the function exists indeed ...
>         If aResult = 0 Then
>             '...get a pointer to the callback function based on
>             'the strFunctionID argument of the GetFuncID function
>              aResult = GetAddr(hProject:=CurrentVBPro­ject, _
>                                strFunctionID:=strFunctionID, _
>                                lpfnAddressOf:=AddressOfFuncti­on)
>             'if we've got the pointer pass it to the result of the function
>             If aResult = 0 Then
>                 AddrOf = AddressOfFunction
>             End If
> 
>        End If
> 
>    End If
> 
> End Function
> 
> 
> '-----------------------------­------------------------------­--------------
> --
> Public Function AddrOf_Callback_Routine() As Long
> '-----------------------------­------------------------------­--------------
> --
> 'Office97 VBE does not recognise the AddressOf operator;
> 'however, it does not raise a compile-error ...
> '-----------------------------­------------------------------­--------------
> --
>     AddrOf_Callback_Routine = vbaPass(AddressOf cbkRoutine)
> End Function
> 
> 
> '-----------------------------­------------------------------­--------------
> --
> Private Function vbaPass(AddressOfFunction As Long) As Long
> '-----------------------------­------------------------------­--------------
> --
>     vbaPass = AddressOfFunction
> End Function
> 
> 
> 
> 
> 
> 
> 
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> "Mark" <Mark@discussions.microsoft.com> wrote in message
> news:A8EC77F7-14D8-4E3A-B683-004E549D1037@microsoft.com...
> > Hi all,
> > I can enter the time in a cell by using the =now() and can update it by
> > hitting the F9 key but how can I make it change automatically to always
> > remain the same as the computer clock.  I would imagine I need a macro to
> do
> > it ...can someone please help me.
> >
> > Thanks....Mark
> 
> 
> 
0
PeterRooney (105)
5/11/2005 1:36:08 PM
It looks like when you run this sub:

Sub StopClock()

The clock should stop.

(Untested--but it makes sense from the name of the sub <bg>.)



Peter Rooney wrote:
> 
> Bob,
> 
> This is a great piece of code - except that I can get it to start, but I
> can't get it to stop! No error messages are displayed, but the clock just
> keeps on runnin' !
> 
> I'm running Excel 9.0.4402 SR1 on Windows 2000 5.00.2195 SP3
> 
> Any ideas?
> 
> Pete
> 
> "Bob Phillips" wrote:
> 
> > Mark,
> >
> > here is one way but it is not simple
> >
> > Add the code below to the modules indicated, and start the clock by running
> > the following code
> >
> >
> >   Set timer = Range("A1")
> >   StartClock
> >
> >
> > To stop the closck, just run the StopClock macro.
> >
> >
> > '-----------------------------­------------------------------­--------------
> > --
> > In one code module add this code
> >
> >
> >
> >
> > Option Explicit
> >
> >
> > Private Declare Function FindWindow Lib "user32" _
> >     Alias "FindWindowA" _
> >    (ByVal lpClassName As String, _
> >     ByVal lpWindowName As String) As Long
> >
> > Private Declare Function SetTimer Lib "user32" _
> >    (ByVal hWnd As Long, _
> >     ByVal nIDEvent As Long, _
> >     ByVal uElapse As Long, _
> >     ByVal lpTimerFunc As Long) As Long
> >
> > Private Declare Function KillTimer Lib "user32" _
> >    (ByVal hWnd As Long, _
> >     ByVal nIDEvent As Long) As Long
> >
> >
> > Public timer As Range
> >
> > Private WindowsTimer As Long
> >
> >
> > Public Function cbkRoutine(ByVal Window_hWnd As Long, _
> >                            ByVal WindowsMessage As Long, _
> >                            ByVal EventID As Long, _
> >                            ByVal SystemTime As Long) As Long
> > Dim CurrentTime As String
> >     On Error Resume Next
> >     timer.Value = Format(Now, "Long Time")
> > End Function
> >
> >
> > Sub StartClock()
> >     timer.Value = Format(Time, "Long Time")
> >     fncWindowsTimer 1000, WindowsTimer        '1 sec
> > End Sub
> >
> >
> > Sub StopClock()
> >     fncStopWindowsTimer
> > End Sub
> >
> >
> > Sub RestartClock()
> >     fncWindowsTimer 1000, WindowsTimer        '1 sec
> > End Sub
> >
> >
> > Public Function fncWindowsTimer(TimeInterval As Long, WindowsTimer As Long)
> > As Boolean
> >     WindowsTimer = 0
> >     'if Excel2000 or above use the built-in AddressOf operator to
> >     'get a pointer to the callback function
> >     If Val(Application.Version) > 8 Then
> >         WindowsTimer = SetTimer(hWnd:=FindWindow("XLM­AIN",
> > Application.Caption), _
> >                                 nIDEvent:=0, _
> >                                 uElapse:=TimeInterval, _
> >                                 lpTimerFunc:=AddrOf_Callback_Routine)
> >     Else 'use K.Getz & M.Kaplan function to get a pointer
> >         WindowsTimer = SetTimer(hWnd:=FindWindow("XLM­AIN",
> > Application.Caption), _
> >                                 nIDEvent:=0, _
> >                                 uElapse:=TimeInterval, _
> >                                 lpTimerFunc:=AddrOf("cbkRoutin­e"))
> >     End If
> >
> >
> >     fncWindowsTimer = CBool(WindowsTimer)
> >
> >     DoEvents
> >
> > End Function
> >
> >
> > Public Function fncStopWindowsTimer()
> >     KillTimer hWnd:=FindWindow("XLMAIN", Application.Caption), _
> >               nIDEvent:=0 'WindowsTimer
> > End Function
> >
> > '-----------------------------­------------------------------­--------------
> > --
> >
> >
> > 'In another code module add this code
> >
> >
> > Option Explicit
> >
> > Private Declare Function GetCurrentVbaProject Lib "vba332.dll" _
> >     Alias "EbGetExecutingProj" _
> >    (hProject As Long) As Long
> >
> > Private Declare Function GetFuncID Lib "vba332.dll" _
> >     Alias "TipGetFunctionId" _
> >    (ByVal hProject As Long, _
> >     ByVal strFunctionName As String, _
> >     ByRef strFunctionID As String) As Long
> >
> > Private Declare Function GetAddr Lib "vba332.dll" _
> >     Alias "TipGetLpfnOfFunctionId" _
> >    (ByVal hProject As Long, _
> >     ByVal strFunctionID As String, _
> >     ByRef lpfnAddressOf As Long) As Long
> >
> >
> > '-----------------------------­------------------------------­--------------
> > --
> > Public Function AddrOf(CallbackFunctionName As String) As Long
> > '-----------------------------­------------------------------­--------------
> > --
> > 'AddressOf operator emulator for Office97 VBA
> > 'Authors: Ken Getz and Michael Kaplan
> > '-----------------------------­------------------------------­--------------
> > --
> > Dim aResult As Long
> > Dim CurrentVBProject As Long
> > Dim strFunctionID As String
> > Dim AddressOfFunction As Long
> > Dim UnicodeFunctionName As String
> >
> >
> >     'convert the name of the function to Unicode system
> >     UnicodeFunctionName = StrConv(CallbackFunctionName, vbUnicode)
> >
> >     'if the current VBProjects exists...
> >     If Not GetCurrentVbaProject(CurrentVB­Project) = 0 Then
> >         '...get the function ID of the callback function, based on its
> >         'unicode-converted name, to ensure that it exists
> >          aResult = GetFuncID(hProject:=CurrentVBP­roject, _
> >                              strFunctionName:=UnicodeFuncti­onName, _
> >                              strFunctionID:=strFunctionID)
> >         'if the function exists indeed ...
> >         If aResult = 0 Then
> >             '...get a pointer to the callback function based on
> >             'the strFunctionID argument of the GetFuncID function
> >              aResult = GetAddr(hProject:=CurrentVBPro­ject, _
> >                                strFunctionID:=strFunctionID, _
> >                                lpfnAddressOf:=AddressOfFuncti­on)
> >             'if we've got the pointer pass it to the result of the function
> >             If aResult = 0 Then
> >                 AddrOf = AddressOfFunction
> >             End If
> >
> >        End If
> >
> >    End If
> >
> > End Function
> >
> >
> > '-----------------------------­------------------------------­--------------
> > --
> > Public Function AddrOf_Callback_Routine() As Long
> > '-----------------------------­------------------------------­--------------
> > --
> > 'Office97 VBE does not recognise the AddressOf operator;
> > 'however, it does not raise a compile-error ...
> > '-----------------------------­------------------------------­--------------
> > --
> >     AddrOf_Callback_Routine = vbaPass(AddressOf cbkRoutine)
> > End Function
> >
> >
> > '-----------------------------­------------------------------­--------------
> > --
> > Private Function vbaPass(AddressOfFunction As Long) As Long
> > '-----------------------------­------------------------------­--------------
> > --
> >     vbaPass = AddressOfFunction
> > End Function
> >
> >
> >
> >
> >
> >
> >
> >
> > --
> >  HTH
> >
> > Bob Phillips
> >
> > "Mark" <Mark@discussions.microsoft.com> wrote in message
> > news:A8EC77F7-14D8-4E3A-B683-004E549D1037@microsoft.com...
> > > Hi all,
> > > I can enter the time in a cell by using the =now() and can update it by
> > > hitting the F9 key but how can I make it change automatically to always
> > > remain the same as the computer clock.  I would imagine I need a macro to
> > do
> > > it ...can someone please help me.
> > >
> > > Thanks....Mark
> >
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
5/11/2005 11:26:30 PM
Reply:

Similar Artilces:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF($B$1:$B$10=B1,$A$1:$A$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

question about Time
How to make the time result for example if it�s ( 1:01 ) or higher shows only as ( 1:00 ) and if it�s Lower like ( 0:59 ) or less it will show the same result in this case ( 0:59 ) Any idea & suggestions. Thanks, almufadda@hotmail.com ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Using Ron deBruin's google addin and asking for subject round time, I get http://tinyurl.com/wgua -- Don Guillett SalesAid Software donaldb@281.com "saud" <saud.xgc4...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

Time Clock Systems
Does anyone have a recommendation for a time clock system that integrates well with GP? On Oct 5, 10:20 am, kcd <k...@discussions.microsoft.com> wrote: > Does anyone have a recommendation for a time clock system that integrates > well with GP? We just implemented Time Matrix by Business Computers (www.business- computers.com) and are very happy with it. We implemented quickly the hardware wasn't propietary or complicated so we were able to source our own stuff. Troy I can speak highly of Unitime's time and attendance system. They are a relatively low cost solution t...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

Adding a word to the end of other words at the same time
I was wondering if there was a way to add a word to the end or beginning of multiple other words in Excel. Example; say I have these 3 words.... Alpha Beta Tera Now I want to add LLC to the end of each word but I want to change them all at the same time. Like Alpha LLC Beta LLC Tera LLC Is there a way to do that? Phil Its Excel 2003 try Sub addtexttoend() For Each c In Selection c.Value = c & " xxx" Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "phil" <ptukey@charter.net> wrote in message news:1125340358.873337.4240@g44g2000cwa.googlegroup...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

How to Identify Records with Overlapping Dates
I need to be able to systematically identify any instance where there are overlapping date ranges in a data set. I need to pull records like those listed below out of a larger data set. I previously posted a question similar to this and was advised to pull the same table in a query, match on Member Number, and qualify that the product code from TableA did not match product code in TableA_1 where the Begin Date TableA was < Term date TableA_1 and TableA_1 Begin Date < TableA End Date. Which worked great for me finding overlapping records where the product was different, bu...

Outlook 2003
Hi Is it possible to order the deleted items by date of deletion in outlook 2003? Many thanks Lee Lee Atkinson <leeatkinsonlincs@hotmail.com> wrote: > Is it possible to order the deleted items by date of deletion in > outlook 2003? Use the Field Chooser to add the Modified time to the Deleted Items folder header bar (the one that says "From", "Subject", "Received", etc. and sort on that time. Deleting an item is a modification, so I would expect the modified date to be affected, giving you the most accurate value of the item's deleted time....