| View previous topic :: View next topic |
| Author |
Message |
aloarjr810
OOo Enthusiast
Joined: 04 Jun 2008
Posts: 113
|
 Posted: Wed Jul 21, 2010 2:00 pm Post subject: [Solved] Another possible timestamp formula |
 |
|
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 |
|
 |
stevesaunders
OOo Advocate
Joined: 26 Dec 2009
Posts: 408
|
| Posted: Fri Jul 23, 2010 8:53 am Post subject: converting formulas |
|
|
comma or semi colon?!
_________________
Steve ~ Tennessee USA
Mac OS X 10.6.2 Open Office 3.1 |
|
| Back to top |
|
|
aloarjr810
OOo Enthusiast
Joined: 04 Jun 2008
Posts: 113
|
| Posted: Fri Jul 23, 2010 9:39 am Post subject: |
|
|
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 |
|
|
aloarjr810
OOo Enthusiast
Joined: 04 Jun 2008
Posts: 113
|
| Posted: Fri Jul 23, 2010 10:36 am Post subject: |
|
|
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 |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Fri Jul 23, 2010 8:39 pm Post subject: |
|
|
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 |
|
|
|
|
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
|
