Returning a date in a cell, but leave blank if no date entered

[lojanica]

Hi All,
I am trying to automate some process and minimise data entry using formulas

Does anyone know if you could populate one cell with today date if other cell contain data otherwise keep it blank but to keep day date entry?

For example: B1=IF(ISBLANK(A1),"",TODAY()) - how do i keep date in B1 when data was entered as date will change with current formula to date i open document again.

Thank you

​

 

[myall_blues]

Not without using VBA as far as I know. There are several VBA options, probably the easiest being a custom function.

Regards

Murray

 

[lojanica]

Not without using VBA as far as I know. There are several VBA options, probably the easiest being a custom function.

Regards

Murray

Hi Murray,
Thank for getting back and do you minde sharing any of those options?
Regards
Milan

 

[myall_blues]

Hi Milan
For example, the post by bearcub at this thread would do exactly what you require. In your example the usage would be in B1
=DATESTAMP(A1)
It's not perfect because of the volatile nature of Now(), so take note of the caution in the original posting.

Regards

Murray

 

[lojanica]

Hi Milan
For example, the post by bearcub at this thread would do exactly what you require. In your example the usage would be in B1
=DATESTAMP(A1)
It's not perfect because of the volatile nature of Now(), so take note of the caution in the original posting.

Regards

Murray

Hi Murray
Thanks for the above. I found the code below works best for me.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If Len(Range("B1").Value) = 0 Then
Range("B1").Value = Now()
End If
End If
End Sub

In the above code, B1 will be autofill with the date if A1 is not blank, which works great.
However, I would like to apply this to a wide range, such as B1:B88, depending on the data entry in A1:A88.
For example, when data is entered in A25, then B25 is auto-filed with the date. My initial thought was that I could use the below code, but it did not work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A88")) Is Nothing Then
If Len(Range("B1:B88").Value) = 0 Then
Range("B1:B88").Value = Now()
End If
End If
End Sub

I can repeat the initial code 88 times; however, I am sure there is a more efficient way to do it
Would you know how to adopt code for a broader range?

I apologize if it is a silly question; however, as you can see, I am still beginer with this

Thanks
Milan

 

[myall_blues]

There are no silly questions - we all have to start somewhere.

You need to find out which row in column A the intersection occurred in, then make the change in column B in the same row.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRow As Variant
    If Not Application.Intersect(Target, Range("A1:A88")) Is Nothing Then
        MyRow = Intersect(Target, Range("A1:A88")).Row
        If Len(Range("B" & MyRow).Value) = 0 Then
            Range("B" & MyRow).Value = Now()
        End If
    End If
End Sub

Regards

Murray

 

[lojanica]

There are no silly questions - we all have to start somewhere.

You need to find out which row in column A the intersection occurred in, then make the change in column B in the same row.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRow As Variant
    If Not Application.Intersect(Target, Range("A1:A88")) Is Nothing Then
        MyRow = Intersect(Target, Range("A1:A88")).Row
        If Len(Range("B" & MyRow).Value) = 0 Then
            Range("B" & MyRow).Value = Now()
        End If
    End If
End Sub

Regards

Murray

Thank y

 

[lojanica]

Works perfectly, thanks

 

[Peter_SSs]

Thank y

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.