Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 6th 08, 02:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 9
Default Need a Static Timestamp Formula

I'm trying to develop a worksheet using Mobile Excel - and among that
version of Excel's 'virtues' is that it doesn't do / support / allow
macros.

Therefore, I'm reduced to trying to come up with a cell formula that
evaluates to a timestamp (date and time) when another cell gets a
value put into it - ant that timestamp doesn't change.

This is a piece of cake if you can use macros, but I have yet to come
up with a way to do this that doesn't recalculate / update all the
timestamp formulas when a recalculation takes place (I'm testing that
by using F9).

Has anyone ever cracked this nut? If the explanation isn't clear,
please ask questions and I'll elaborate...

Thanks!

James


  #2   Report Post  
Old April 6th 08, 03:09 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,355
Default Need a Static Timestamp Formula

I'm not sure if this is what you are looking for or if it works in Mobile
Excel, but try CTRL ;
--
HTH,
Barb Reinhardt



" wrote:

I'm trying to develop a worksheet using Mobile Excel - and among that
version of Excel's 'virtues' is that it doesn't do / support / allow
macros.

Therefore, I'm reduced to trying to come up with a cell formula that
evaluates to a timestamp (date and time) when another cell gets a
value put into it - ant that timestamp doesn't change.

This is a piece of cake if you can use macros, but I have yet to come
up with a way to do this that doesn't recalculate / update all the
timestamp formulas when a recalculation takes place (I'm testing that
by using F9).

Has anyone ever cracked this nut? If the explanation isn't clear,
please ask questions and I'll elaborate...

Thanks!

James


  #3   Report Post  
Old April 6th 08, 06:42 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default Need a Static Timestamp Formula

From John McGimpsey...................

Using circular references and worksheet functions
You can use a circular reference to enter the time when a change is made in
another cell, then maintain that time. Choose Tools/Options/Calculation
(Preferences/Calculation for Macs) and check the Iteration checkbox. Then, if
your target cell is A1 and you want the date/time to appear in B1, enter this in
B1:

=IF(A1="","",IF(B1="",NOW(),B1))

Format B1 as you wish to display date, time, or both. If A1 is initially blank,
B1 will return a null string (""). When a value is entered into A1, B1 will
evaluate as "", therefore NOW() will be returned. After that (as long as A1
remains populated), B1 will evaluate to a date/time and therefore will return
the value in B1 - i.e., the date/time.

.................................................. .


Gord Dibben MS Excel MVP

On Sun, 6 Apr 2008 06:55:54 -0700 (PDT), wrote:

I'm trying to develop a worksheet using Mobile Excel - and among that
version of Excel's 'virtues' is that it doesn't do / support / allow
macros.

Therefore, I'm reduced to trying to come up with a cell formula that
evaluates to a timestamp (date and time) when another cell gets a
value put into it - ant that timestamp doesn't change.

This is a piece of cake if you can use macros, but I have yet to come
up with a way to do this that doesn't recalculate / update all the
timestamp formulas when a recalculation takes place (I'm testing that
by using F9).

Has anyone ever cracked this nut? If the explanation isn't clear,
please ask questions and I'll elaborate...

Thanks!

James


  #4   Report Post  
Old January 11th 20, 06:29 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2020
Posts: 1
Default Need a Static Timestamp Formula

Use this macro


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Static date formula Dude3966 Excel Discussion (Misc queries) 3 February 21st 08 09:18 AM
How to convert a static formula to dynamic formula ? Pisistratus Excel Worksheet Functions 3 July 5th 07 01:54 PM
Static Timestamp without using Macros Maeglin Excel Worksheet Functions 2 May 25th 07 11:22 AM
Excel formula timestamp for a data entry of a referenced cell jmasaki Excel Worksheet Functions 1 July 3rd 06 02:19 AM
static time in formula sklomstad Excel Worksheet Functions 1 February 17th 06 06:02 PM


All times are GMT +1. The time now is 11:20 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017