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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
If you format column B as date enter this in B5: =IF(A5 > 1,NOW(),"")

Let me know how that goe
 
If you format column B as date enter this in B5: =IF(A5 > 1,NOW(),"")

Let me know how that goe
 
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?
 
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?
 
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(A5>1,TODAY())

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


Gord Dibben MS Excel MVP
 
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(A5>1,TODAY())

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


Gord Dibben MS Excel MVP
 
If you select cell B5 and look in the bottom left corner of the cel
there should be a little black box. Hover your mouse over it and you
cursor should change to a + left, click your mouse and drag dow
however far you want to go. This should do it automatically for yo
 
If you select cell B5 and look in the bottom left corner of the cel
there should be a little black box. Hover your mouse over it and you
cursor should change to a + left, click your mouse and drag dow
however far you want to go. This should do it automatically for yo
 
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
 
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
 
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
 
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
 
Thank you very much.
Problem solved.

Shaggy


Gord Dibben said:
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
Click to expand...
 
Thank you very much.
Problem solved.

Shaggy


Gord Dibben said:
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
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Need Countifs Formula Help 0
Excel Average dates help 0
Comparison of columns using Conditional Formatting 3
conditional formatting based on different cells with more conditio 1
How to check if the values in a range of cells are greater than 0 3
DATE STATIC 10
Minimum Date Greater than Today() 6
Static current date based on when data in another column was enter 3

Back
Top