Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if data in one cell, then date in adjacent cell | Excel Worksheet Functions | |||
extract name when a date in another cell gets near | Excel Worksheet Functions | |||
Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
GET.CELL | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |