Return DATE if a Cell is more than 1

R

rinku.yadav

New Member

Joined

Oct 28, 2009

Messages

23

• Dec 4, 2009

• #2

formula would be =if(A1>1,today(),)
but keep the cell formating in date format for that

 

Upvote 0

A

aiwnjoo

Well-known Member

Joined

Jul 30, 2009

Messages

598

• Dec 7, 2009

• #3

Thanks, this is the working formula im using;

=IF(N19162="","",IF(N19162>1,TODAY(),))

This auto inputs the date when the other users complete certain cells so i know what date they have done it, but if they modify it a day later then it changes to the current date, is there a way to keep it static to when it was first changed?

Thanks,

 

Upvote 0

A

aiwnjoo

Well-known Member

Joined

Jul 30, 2009

Messages

598

• Dec 7, 2009

• #4

any idea?

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Dec 7, 2009

• #5

You would need to enable Iteration so as to permit circular references (Max Iteration set to 1) which in turn would allow you to reference the formula cell itself...

If we assume your formula is in say O19162 then

=IF(N19162="","",IF(AND(N19162>1,N(O19162)=0),TODAY(),O19162))

The date stamp in the above case would only reset if N19162 were cleared... not stipulated if that's desired or not, if you want it to persist forever more (unless formula itself deleted/re-entered) then perhaps

=IF(N(O19162),O19162,IF(N19162>1,TODAY(),""))

Most would opt to utilise VBA (Change Event) in preference to Circular Referencing.

 

Upvote 0

A

aiwnjoo

Well-known Member

Joined

Jul 30, 2009

Messages

598

• Dec 8, 2009

• #6

Worked like a charm, thanks.

 

Upvote 0

A

aiwnjoo

Well-known Member

Joined

Jul 30, 2009

Messages

598

• Dec 8, 2009

• #7

Actually we have a problem;

This is the working formula;

=IF(N19163="","",IF(N(A19163),A19163,IF(N19163>1,TODAY(),"")))

It works fine for me but when i share the sheet for others to update things the Iteration is off for them, anyway to fix this?

Thanks,

 

Upvote 0

A

aiwnjoo

Well-known Member

Joined

Jul 30, 2009

Messages

598

• Dec 9, 2009

• #8

Bump

 

Upvote 0

A

aiwnjoo

Well-known Member

Joined

Jul 30, 2009

Messages

598

• Dec 9, 2009

• #9

Hi, i created a macro so that when its shared it Auto_Runs this macro to turn Iteration to 1 but Macros won't run in a shared workboook????? It just keeps showing a Circular error to the other users.

Code:

Sub Auto_Run()
'
' Auto_Run Macro
' Auto_Run Macro for Auto Date Iteration!
'
'
    Range("A1").Select
    With Application
        .Iteration = True
        .MaxChange = 0.001
    End With
    ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

The formula code is;

=IF(N19793="","",IF(N(A19793),A19793,IF(N19793>0,TODAY(),"")))

 

Upvote 0

A

aiwnjoo

Well-known Member

Joined

Jul 30, 2009

Messages

598

• Dec 9, 2009

• #10

Ok i thinnk i almost fixed it, i just adding the following to ThisWorkbook module;

Code:

Private Sub Workbook_Open()
With Application
.Iteration = True
.MaxChange = 0.001
End With
End Sub

Now it works in shared mode but it still shows a Circular referencing error so the other users have to click Cancel or Ok, anyway to remove this?

I have iteration set to 1 but Column A1:A65000 contains the conflicting formula, should i be setting iteration higher?

 

Upvote 0