=now() issue with excel for web

[BigBoiJack]

I currently have a document that is shared via Onedrive to multiple other users who all use it simultaneously.
within this Spreedsheet is a simple if formula

O3=IF(K3=" ", " ",IF(K3="vacant"," ", " ",IF(K3="leave", " ", " ",IF(K3<>R3,R1,O3))))

where
K3= current status of a position (eg leave, vacant, current etc.) selected from a list
R3= the following fomula "=IF(K3=R3,R3,K3)"
R1= now()

pretty much what i want is when the status changes in cell K3 to input a time stamp into O3 unless nothing/vacant/leave is the status then delete the time stamp.
this is working fine on my spreadsheet both in excel and the web version. but once the document is shared with other users so they can also edit statuses i start getting a strange issue.
when the page is refreshed the times in cell O3 will all change by about 12 hours and i will notice the R1 cell time will change momentarily to the 12hour difference before changing back to the current time.

i am unsure what is causing this issue, formula error, or maybe a setting within excel for web?

thankyou for any help

 

[Jeffrey Mahoney]

My first take on this is that you have a reciprocal formula in R3. Unless you stated your cell address wrong.

 

[Jeffrey Mahoney]

The same for Cell O3. The formula is referencing itself. Anytime a workbook is recalculated, Now() will change. If you wanted a static date when the person changed cell K3, that won't happen.

 

[Jeffrey Mahoney]

What you need is for VBA to trigger a cell change and update the date in cell K3 to the current date.

 

[BigBoiJack]

due to the fact that i am using excel for the web VBA isnt an option. these formulas work perfectly until i share the workbook with other users

 

[Jeffrey Mahoney]

I'm pretty sure that circular references will fail for the online version just as they do for a true workbook. Making the result of a cell equal itself is a circular reference. The colored cells show this.
This formula in cell R3: =IF(K3=R3,R3,K3) is saying if I am equal to another cell then equal me, else if I'm different than the other cell, make me equal to that cell
I know you are asking for it to only change if they are different, but Excel doesn't work that way.

O3[/COLOR]=IF(K3=" ", " ",IF(K3="vacant"," ", " ",IF(K3="leave", " ", " ",IF(K3<>R3,R1,O3))))

where
K3= current status of a position (eg leave, vacant, current etc.) selected from a list
R3= the following fomula "=IF(K3=R3,R3,K3)"
R1= now()

 

[Michael M]

Just as an aside the problem, I wouldn't return a space as a result for the formula, as it may cause issues later on.
Myabe this way

=IF(OR(K3="", K3="vacant",K3="leave"),"",IF(K3<>R3,R1,O3))

 

[Jeffrey Mahoney]

Michael, the formula you just posted also has a circular reference because it is in the cell O3

 

[Jeffrey Mahoney]

I can't seem to get around the problem. There needs to be a static value in this or else it will continue to change.

 

[Michael M]

Michael, the formula you just posted also has a circular reference because it is in the cell O3

I didn't suggest the formula was in O3.....I simply made an adjustment to the formula provided....