=now() issue with excel for web

BigBoiJack

New Member
Joined
Dec 3, 2019
Messages
3
Office Version
365, 2019
Platform
Windows, MacOS
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

Well-known Member
Joined
May 31, 2015
Messages
1,591
My first take on this is that you have a reciprocal formula in R3. Unless you stated your cell address wrong.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,591
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.
 

BigBoiJack

New Member
Joined
Dec 3, 2019
Messages
3
Office Version
365, 2019
Platform
Windows, MacOS
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

Well-known Member
Joined
May 31, 2015
Messages
1,591
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.

[COLOR=rgb(184 said:
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

Well-known Member
Joined
Oct 27, 2005
Messages
17,957
Office Version
2013
Platform
Windows
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

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

Forum statistics

Threads
1,077,782
Messages
5,336,284
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top