OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Another possible timestamp formula

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
aloarjr810
OOo Enthusiast
OOo Enthusiast


Joined: 04 Jun 2008
Posts: 113

PostPosted: Wed Jul 21, 2010 2:00 pm    Post subject: [Solved] Another possible timestamp formula Reply with quote

There are a couple of other timestamp threads going, but I thought this might need it's own.

I ran across this on another site, Its a timestamp that only changes when you change the cell value.
It doesn't change when you save or open or make a change anywhere else on the sheet or force a recalculate and you
don't have to clear the data cell just select the cell start typing and it's all done with formulas and No macros.
Just A timestamp formula and 2 helper formulas (which you could hide).

The only thing is , it seems to only work in Excel. I've tried it several times in Excel and it seem to work.

But if you open it in OpenOffice all the timestamps are set to current time. You can reset them, but if you save and reopen it resets to current time.

If theres a setting thats affecting it, I can figure out what it is.

I assume Excel is handling time calculations in some way differently than OpenOffice.So that it lets it work in excel. I can't find any setting's in OpenOffice that seem to effect it.

I thought maybe this could help someone figure out how make one or to make this one work in OOo.

Heres the Excel formulas:
Code:
Cell A2 has your data


Data backup formula in B2
Code:
=IF(A2<>"",IF(B2="",0&" - "&A2,IF(RIGHT(B2,LEN(B2)-4)=""&A2,B2,MOD(LEFT(B2,1)+1,10)&" - "&A2)),"")


Timestamp Backup formula in C2
Code:
=IF(A2<>"",IF(C2="",0&" - "&NOW(),IF(LEFT(C2,1)=LEFT(B2,1),C2,LEFT(B2,1)&" - "&NOW())),"")


Timestamp formula in D2
Code:
=IF(C2<>"",VALUE(RIGHT(C2,LEN(C2)-4)),"")



Code:
Iteration checked-Max Iteration 100-Max change 0.001


This is what it looks like in Excel
The data here is numbers, but it works with text too.


Last edited by aloarjr810 on Fri Jul 23, 2010 3:52 pm; edited 2 times in total
Back to top
View user's profile Send private message
stevesaunders
OOo Advocate
OOo Advocate


Joined: 26 Dec 2009
Posts: 408

PostPosted: Fri Jul 23, 2010 8:53 am    Post subject: converting formulas Reply with quote

comma or semi colon?!
_________________
Steve ~ Tennessee USA
Mac OS X 10.6.2 Open Office 3.1
Back to top
View user's profile Send private message
aloarjr810
OOo Enthusiast
OOo Enthusiast


Joined: 04 Jun 2008
Posts: 113

PostPosted: Fri Jul 23, 2010 9:39 am    Post subject: Reply with quote

Those are the excel formulas which use commas. I didn't want to post any that I messed with in case I made a mistake in changing them for OOo.

In openoffice I used semicolons.
Back to top
View user's profile Send private message
aloarjr810
OOo Enthusiast
OOo Enthusiast


Joined: 04 Jun 2008
Posts: 113

PostPosted: Fri Jul 23, 2010 10:36 am    Post subject: Reply with quote

Well I believe I got it going now!

I retyped it all in just to make sure it was right and when I got done I got a err:502. So I saved it and closed, but when I opened back up it was running.

Heres the code

Cell A1 is your data
Iterations must be checked

Cell B2
Code:
=IF(A2<>"";IF(B2="";0&" - "&A2;IF(RIGHT(B2;LEN(B2)-4)=""&A2;B2;MOD(LEFT(B2;1)+1;10)&" - "&A2));"")


Cell C2
Code:
=IF(A2<>"";IF(C2="";0&" - "&NOW();IF(LEFT(C2;1)=LEFT(B2;1);C2;LEFT(B2;1)&" - "&NOW()));"")


Cell D2 the Timestamp
Code:
=IF(C2<>"";VALUE(RIGHT(C2;LEN(C2)-4));"")


Seems to be going ok so far. A long way to go for a updateable non-volatile timestamp
but it did it without macro.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Fri Jul 23, 2010 8:39 pm    Post subject: Reply with quote

Hi aloarjr810,
I was trying to get it to work in Calc the other day and got nowhere.
After I did the necessary syntax changes I got error 502 so I tried array entering the formulas. This did get rid of the errors, however, closing and reopening updated the time stamps.
From your last post I now see that the formulas are standard and the error goes away after save, close then re-open.
Great work!

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group