Auto Date in a cell if other cell is greater than 1
 

Can anyone help,
I need a formula to automatically put the current date into a cell if the
cell next to it is greater than 1
EG. If 'A5' = 1000 then 'B5' = Todays Date

Auto Date in a cell if other cell is greater than 1
 

If you format column B as date enter this in B5: =IF(A5 1,NOW(),"")

Let me know how that goes


--
Steel Monkey
------------------------------------------------------------------------
Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
View this thread: http://www.excelforum.com/showthread...hreadid=573616

Auto Date in a cell if other cell is greater than 1
 

That worked really well and easily, Thank you heaps.
Is there a way to fill the formula down to each cell without changing the
"A5" to "A6", "A7" manually?

"Steel Monkey" wrote:


If you format column B as date enter this in B5: =IF(A5 1,NOW(),"")

Let me know how that goes


--
Steel Monkey
------------------------------------------------------------------------
Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
View this thread: http://www.excelforum.com/showthread...hreadid=573616

Auto Date in a cell if other cell is greater than 1
 

Do you want it to be a static date that doesn't change tomorrow?

If so, you will need event code.

If not, just enter in B5 =IF(A51,TODAY())

If A5 is still greater than 1 tomorrow, the result will update.


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 18:47:01 -0700, Shaggy
wrote:

Can anyone help,
I need a formula to automatically put the current date into a cell if the
cell next to it is greater than 1
EG. If 'A5' = 1000 then 'B5' = Todays Date

Auto Date in a cell if other cell is greater than 1
 

If you select cell B5 and look in the bottom left corner of the cell
there should be a little black box. Hover your mouse over it and your
cursor should change to a + left, click your mouse and drag down
however far you want to go. This should do it automatically for you


--
Steel Monkey
------------------------------------------------------------------------
Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
View this thread: http://www.excelforum.com/showthread...hreadid=573616

Auto Date in a cell if other cell is greater than 1
 

Thanks again,
Yes, I need the date in "B5" to be Static according to the date that the
data was entered into it.

EG: "A5" = 19000 (Which i type in on 21 Aug 2006) so "B5" = 21 Aug 2006
"A6" = 20001 (Which i type in on 23 Aug 2006) so "B6' = 23 Aug 2006
but "A5" should stll be dated as 21 Aug 2006

Thanks again for your help

"Gord Dibben" wrote:

Do you want it to be a static date that doesn't change tomorrow?

If so, you will need event code.

If not, just enter in B5 =IF(A51,TODAY())

If A5 is still greater than 1 tomorrow, the result will update.


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 18:47:01 -0700, Shaggy
wrote:

Can anyone help,
I need a formula to automatically put the current date into a cell if the
cell next to it is greater than 1
EG. If 'A5' = 1000 then 'B5' = Todays Date

Auto Date in a cell if other cell is greater than 1
 

Then you will need event code to enter the static date.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If IsNumeric(Excel.Range("A" & n).Value) And _
Excel.Range("A" & n).Value 1 Then
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is event code and needs to be copied to a worksheet module.

Right-click on the sheet tab and "View Code".

Paste the above into that module.

Any value 1 entered in any cell in column A will trigger a date stamp in
corresponding cell in column B.


Gord

On Sun, 20 Aug 2006 20:40:02 -0700, Shaggy
wrote:

Thanks again,
Yes, I need the date in "B5" to be Static according to the date that the
data was entered into it.

EG: "A5" = 19000 (Which i type in on 21 Aug 2006) so "B5" = 21 Aug 2006
"A6" = 20001 (Which i type in on 23 Aug 2006) so "B6' = 23 Aug 2006
but "A5" should stll be dated as 21 Aug 2006

Thanks again for your help

"Gord Dibben" wrote:

Do you want it to be a static date that doesn't change tomorrow?

If so, you will need event code.

If not, just enter in B5 =IF(A51,TODAY())

If A5 is still greater than 1 tomorrow, the result will update.


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 18:47:01 -0700, Shaggy
wrote:

Can anyone help,
I need a formula to automatically put the current date into a cell if the
cell next to it is greater than 1
EG. If 'A5' = 1000 then 'B5' = Todays Date

Auto Date in a cell if other cell is greater than 1
 

Thank you very much.
Problem solved.

Shaggy


"Gord Dibben" wrote:

Then you will need event code to enter the static date.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If IsNumeric(Excel.Range("A" & n).Value) And _
Excel.Range("A" & n).Value 1 Then
Excel.Range("B" & n).Value = Format(Now, "dd mmm yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is event code and needs to be copied to a worksheet module.

Right-click on the sheet tab and "View Code".

Paste the above into that module.

Any value 1 entered in any cell in column A will trigger a date stamp in
corresponding cell in column B.


Gord

On Sun, 20 Aug 2006 20:40:02 -0700, Shaggy
wrote:

Thanks again,
Yes, I need the date in "B5" to be Static according to the date that the
data was entered into it.

EG: "A5" = 19000 (Which i type in on 21 Aug 2006) so "B5" = 21 Aug 2006
"A6" = 20001 (Which i type in on 23 Aug 2006) so "B6' = 23 Aug 2006
but "A5" should stll be dated as 21 Aug 2006

Thanks again for your help

"Gord Dibben" wrote:

Do you want it to be a static date that doesn't change tomorrow?

If so, you will need event code.

If not, just enter in B5 =IF(A51,TODAY())

If A5 is still greater than 1 tomorrow, the result will update.


Gord Dibben MS Excel MVP

On Sun, 20 Aug 2006 18:47:01 -0700, Shaggy
wrote:

Can anyone help,
I need a formula to automatically put the current date into a cell if the
cell next to it is greater than 1
EG. If 'A5' = 1000 then 'B5' = Todays Date