An updating date that doesn't change previous dates

[DCCC93]

Hi there,

I am trying to create a document where new field entries will be added everyday with differing amounts of new entries each day as it depends how many calls we receive from customers that we need to log.

What I ideally would like is for today's date to automatically appear on a new field but for the previous dates on rows above not to update. I.e. using Today() gives me today's date which is good, however, when I then go in to add new entries tomorrow all of today's inputs dates will change to tomorrow's date as well as any new inputs.

Does anybody know of a formula/formatting to make this happen? Basically a live update that doesn't change the previous fields dates?

I have tried alternatives such as macros and the calendar input method but using macros isn't ideal to then communicate with my team. Ideally, some sort of formula to control this date process would be great.

I hope that makes sense,

Thanks
DC

 

[Fishboy]

Hi there,

I am trying to create a document where new field entries will be added everyday with differing amounts of new entries each day as it depends how many calls we receive from customers that we need to log.

What I ideally would like is for today's date to automatically appear on a new field but for the previous dates on rows above not to update. I.e. using Today() gives me today's date which is good, however, when I then go in to add new entries tomorrow all of today's inputs dates will change to tomorrow's date as well as any new inputs.

Does anybody know of a formula/formatting to make this happen? Basically a live update that doesn't change the previous fields dates?

I have tried alternatives such as macros and the calendar input method but using macros isn't ideal to then communicate with my team. Ideally, some sort of formula to control this date process would be great.

I hope that makes sense,

Thanks
DC

Hi DC, welcome to the boards.

Based on what you describe (as in, the desired end result and the fact that teaching the team to use macros would be an issue), I think the best bet would be to use a Worksheet_Change macro which basically does all the work for you and the team don't need to change their work habits.

The following code is added directly to the backend of the sheet in question rather than a standard module and, when a user fills in data on a new row, if the date column was empty it will add the current date automatically with no other action required from the user. Using this method, not only will the user have less to think about but the dates become hard values in their cells and do not want to change themselves on every new calendar day like the formula =Today() does.

To try this out, I would first make a COPY of your workbook to test in to make sure you are happy with what it does. Once you have your copy, right-click on the tab name you want to apply this to, then select View Code. In the new window that opens just copy / paste in the following, remembering to save the document as a macro enabled workbook (.xlsm) afterwards. Without knowing in what column you are wanting the date to go in I have had to improvise, however the specifics of the code are simple enough to change as required:

Private Sub Worksheet_Change(ByVal Target As Range)
' If you update a cell in column B and there is no date in the corresponding row of column A then
If Not Intersect(Target, Range("B:B")) Is Nothing And Range("A" & Target.Row).Value = "" Then
' Insert today's date in the corresponding row of column A
        Range("A" & Target.Row).Value = Date
End If
End Sub

 

[DCCC93]

Hi Fishboy, thanks for the welcome and big thanks for your help!

That works just how I was thinking. When I delete the entry in column B that made the date appear in column a the date remains in column a, I just wondered is there something we can add to this code to make it so that if column b entry then deleted that the date then disappears until something next input in column b?

Thanks

 

[Fishboy]

Hi Fishboy, thanks for the welcome and big thanks for your help!

That works just how I was thinking. When I delete the entry in column B that made the date appear in column a the date remains in column a, I just wondered is there something we can add to this code to make it so that if column b entry then deleted that the date then disappears until something next input in column b?

Thanks

No problem.

The code could be updated to do what you describe as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
' If you update a cell in column B with a value and there is no date in the corresponding row of column A then
If Not Intersect(Target, Range("B:B")) Is Nothing And Target.Value <> "" And Range("A" & Target.Row).Value = "" Then
' Insert today's date in the corresponding row of column A
        Range("A" & Target.Row).Value = Date
' Else if you remove the value from a cell in column B then
ElseIf Not Intersect(Target, Range("B:B")) Is Nothing And Target.Value = "" And Range("A" & Target.Row).Value <> "" Then
' Clear the value from the corresponding row of column A
        Range("A" & Target.Row).ClearContents
End If
End Sub

 

[DCCC93]

You're a legend Fishboy, that has worked perfectly. After days of frustrating myself I'm very glad I came on here to ask - massive help!!

Just to confirm when I go in tomorrow they will all still be saved as "22/01/2016" and then any I add tomorrow will come up as "23/01/2016"?

Apologies for asking - I would change my computer clock to change the date and look myself but unfortunately as I am at work, I am restricted from being able to do so.

Again, thanks so much for your help

DC

 

[Fishboy]

You're a legend Fishboy, that has worked perfectly.

You'll make me blush!

Just to confirm when I go in tomorrow they will all still be saved as "22/01/2016" and then any I add tomorrow will come up as "23/01/2016"?

Apologies for asking - I would change my computer clock to change the date and look myself but unfortunately as I am at work, I am restricted from being able to do so.

Again, thanks so much for your help

DC

I can confirm that the date in the cell will NOT update to tomorrow's date at midnight. The part of the VBA code which says Range("A" & Target.Row).Value = Date means that the cell is updated with the actual date itself. You can confirm this by simply selecting the cell and looking in the formula bar. It should show 22/01/2016 (or whatever formats your dates are set to), where as the formulaic version would show as =Today() in the formula bar.

 

[DCCC93]

That's great, thanks again for all your help.

Have a good day!

DC

 

[DCCC93]

Hi Fishboy,

Hope you are well mate.

Just a quick question on this, I've been using the below code which has been perfect.

Private Sub Worksheet_Change(ByVal Target As Range)' If you update a cell in column B with a value and there is no date in the corresponding row of column A then
If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Value <> "" And Range("B" & Target.Row).Value = "" Then
' Insert today's date in the corresponding row of column A
Range("B" & Target.Row).Value = Date
' Else if you remove the value from a cell in column B then
ElseIf Not Intersect(Target, Range("A:A")) Is Nothing And Target.Value = "" And Range("B" & Target.Row).Value <> "" Then
' Clear the value from the corresponding row of column A
Range("B" & Target.Row).ClearContents
End If
End Sub


Just a quick question though if you wouldn't mind - I get a Run Time Error 13 - Type Mismatch when deleting contents from multiple cells.
If highlights the second line If Not Intersect(Target, Range("A:A")) Is Nothing And Target.Value <> "" And Range("B" & Target.Row).Value = "" Then in the debugger so is it something to do with the Target part?

I can't work out how to correct it and wondered if you might know?

Thanks
DC

 

[DCCC93]

Don't worry mate, I've worked it out now! Thanks anyway.

 

[Fishboy]

Don't worry mate, I've worked it out now! Thanks anyway.

Ah, I was too late in seeing this.

The issue would have been down to Excel handling the "target" cell badly if more than one cell was updated at once.

I have actually rewritten the entire code to handle multiple cells at once, both for filling in and for deleting. Glad to hear you got the issue sorted, but it may be worth trying this out in a COPY of your workbook to see if it is any better suited for handling multiple cells at once:

Private Sub Worksheet_Change(ByVal Target As Range)' Define variable
Dim Cell As Range
    ' If more than one cell is updated at once then...
    If Target.Cells.Count > 1 Then
        ' For each cell that was updated
        For Each Cell In Selection
            ' If the cell was in column A then...
            If Cell.Column = 1 Then
                ' If the new cell value is blank then...
                If Cell.Value = "" Then
                    ' Clear the contents of the adjacent cell in column B
                    Cell.Offset(0, 1).ClearContents
                ' Else if the new cell value is not blank then...
                ElseIf Cell.Value <> "" Then
                    ' If the adjacent cell in column B is blank then...
                    If Cell.Offset(0, 1).Value = "" Then
                        ' Update the adjacent cell in column B with the date
                        Cell.Offset(0, 1).Value = Date
                    End If
                End If
            End If
        ' Check next cell that was updated
        Next Cell
    ' Else
    Else
        ' If only one cell is updated at a time then
        If Target.Cells.Count = 1 Then
            ' If the target cell is in column A then...
            If Target.Column = 1 Then
                ' If the new target value is blank then...
                If Target.Value = "" Then
                    ' Clear the contents of the adjacent cell in column B
                    Target.Offset(0, 1).ClearContents
                ' Else if the new target value is not blank then...
                ElseIf Target.Value <> "" Then
                    ' If the value of the adjacent cell in column B is blank then...
                    If Target.Offset(0, 1).Value = "" Then
                        ' Update the adjacent cell in column B with the date
                        Target.Offset(0, 1).Value = Date
                    End If
                End If
            End If
        End If
    End If
End Sub

That's a whole lot of if's right there!