=now() issue with excel for web

BigBoiJack

New Member
Joined
Dec 3, 2019
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. 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
 
i am unsure if you are struggling to understand the issue, the formula itself is working exactly how i want.
its just when i upload the document online to a OneDrive account and share it with other users it has issues when refreshing the page (via excel online).
Seeing as you still believe my formula is incorrect please see the dropbox link for an example of my spreadsheet (column R would usually be hidden)
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I don't want my language to sound overbearing or demanding. I'm a real logical person. I often speak in black and white. I'm trying to logically outline the problem with circular references.

I really do understand. When I opened the workbook, Excel tells me there is a circular reference. Let's look at how it doesn't work in Excel. When I choose "Option B" in K3, the formula below should fall into the blue portion I highlighted because K3 doesn't equal a space, nor "vacant", nor "leave". Cell R3 has "Vacant". So, IF K3 <> R3 ("Option B" <> "Vacant") then put R1 (which is the date/time). In this case it results as blank. So it's not working.
=IF(K3=" "," ",IF(K3="vacant"," ",IF(K3="leave"," ",IF(K3<>R3,R1,O3))))

Now for the following formula in cell R3:
=IF(K3=R3,R3,K3)
Again K3 = "Option B" and R3 = "Vacant". So, K3=R3=FALSE. The result of the formula should be the value of K3, which it doesn't because it is "Vacant". Not working.

If I press F9 (recalculate), the cell R1 keeps updating over and over. If you're trying to get Cell O3 to only change the date when a user selects a new value in cell K3, this won't happen.

What I'm trying to say is that circular references give strange results.
 
Upvote 0

Forum statistics

Threads
1,223,968
Messages
6,175,677
Members
452,666
Latest member
AllexDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top