Dates in excel

swingkat

New Member
Joined
Sep 18, 2006
Messages
29
Hello,

I'm would like to write a function that automatically writes the date an action is taken. In other words, it would be something like:

=IF(D12="x";TODAY(); ) but the problem is I need the date to remain as a static value, which is why I can't use TODAY or NOW. I'm stumped because the only way to write a static date that I have found is using the keyboard command CTRL+SHIFT+; and I can't include that sequence in the IF function or in VBA.

If anyone could help me with this I would be really grateful as I've been wrestling with this problem for two whole days without any luck. It seems so simple......
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016
Hi

You could use the PasteSpecial function in VBA to Copy and Paste Values for the cell you want to timestamp.

The date cells all have =TODAY() until the value of the cell you want to changes then your code runs and justs copies then pastes values.

HTH


Dave
 

swingkat

New Member
Joined
Sep 18, 2006
Messages
29
Hi Dave,

Thanks for your reply. I did try to make a macro that special pasted the value returned by the today function but the problem I had was that the date would still be updated whenever the macro was run. I’m a fairly novice user of Excel though so your reply makes me wonder if I went about this the wrong way.

To explain what I’m trying to do a bit better: I have a table that lists a number of activities that must be performed in order to solve a given problem. The activities are performed in random order. The idea is to have a column (column D) where the user would mark an x when an activity is completed and then the date the x is marked would be automatically entered in another column (column E). The first thing I tried was the formula I wrote previously: =IF(D12=”x”; TODAY () ; ) but the date would be updated whenever the workbook was opened. Then I moved the IF function to another column (F) and made a macro that special pasted the date value into the E column. The problem is that the dates were updated whenever the macro was run, since I don’t know how to write a program that distinguishes which cells have been timestamped previously. Like I said before, the activities are not performed in any specific order which means that two activities could be performed today and then one next week, and a fourth one the week after that and so on.

I did try to write a function =IF(AND(D12=”x”;E12>0);E12;F12) but then I got confused and I wasn’t sure where to put this function or what it would do, and would this mean I would end up with two hidden columns in order to get the simple date I wanted or what.

Anyway, sorry for rambling on. As you can see I’m pretty muddled up by now. What am I doing wrong? I still think what I want to do sounds so simple….

Thanks for your help!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, swingkat
Welcome to the Board !!!!

this needs some "event"code
this allows for multiple "x"s (in case you want to checkmark mutiple rows at once)
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim CheckRange As Range

Set CheckRange = Intersect(Target, Columns("D"))

If CheckRange Is Nothing Then Exit Sub

Application.EnableEvents = False

    For Each cell In CheckRange
    'you can replace Date by Time or Now
    cell.Offset(0, 1) = IIf(cell = "x", Date, "")
    Next cell

Application.EnableEvents = True

End Sub
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE


another way to go, could be to "doubleclick" in column "D" to get the Time
no "x" would be needed

kind regards,
Erik

EDIT: changed code to "Date" instead of "Now"
Code:
    'you can replace Date by Time or Now
    cell.Offset(0, 1) = IIf(cell = "x", Date, "")
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,013
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

Hi

I think my idea was to build the sheet so as =TODAY() was in your column E then when column D was updated the code for copy/paste specail would run for the specific row.

I'll work on a code and post it back when I find it

KR


Dave
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi

I think my idea was to build the sheet so as =TODAY() was in your column E then when column D was updated the code for copy/paste specail would run for the specific row.

I'll work on a code and post it back when I find it

KR


Dave
Dave,
if I may tell you this: if you are anyway using code, there is no need to use formulas "waiting" to be "frozen" (paste values) by code
you can paste the time or date or both directly into the sheet as "value"

best regards,
Erik
 

swingkat

New Member
Joined
Sep 18, 2006
Messages
29

ADVERTISEMENT

Hello and thanks for the welcome!

Whoa, thanks so much for the code. I never would have been able to come up with something like that on my own.

Many, many thanks! *runs off to try the code in poor, abused spreadsheet*
 

swingkat

New Member
Joined
Sep 18, 2006
Messages
29
Hi again,

I tried the code and it worked like a charm. There are three similar tables to the one I described in the same worksheet, which means that x's are marked in columns L and T besides D. I tried copying and pasting the same code three times and changing the line:

Set CheckRange= Intersect (Target, Columns("D"))

to the same one with L and T, but it didn't work. I also tried writing the three columns in the line:

Set CheckRange= Intersect (Target, Columns ("D:D","L:L",T:T")) but it didn't work either.

Can this code be modified so that I can use it for the three columns?

Thanks again in advance for any help!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
to stick to this code, allowing to place multiple "x"s at once, you can use UNION
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim CheckRange As Range
Dim TC As Long

Set CheckRange = Intersect(Target, Union(Columns("D"), Columns("L"), Columns("T")))

If CheckRange Is Nothing Then Exit Sub

Application.EnableEvents = False

    For Each cell In CheckRange
    'you can replace Date by Time or Now
    cell.Offset(0, 1) = IIf(cell = "x", Date, "")
    Next cell

Application.EnableEvents = True

End Sub

another option would involve checkiing the columnnumber
TC = Target.Column
If TC <> 4 And TC <> 12 And TC <> 20 Then Exit Sub

but needs rewriting the code

greetings from Belgium
Erik
 

swingkat

New Member
Joined
Sep 18, 2006
Messages
29
Thanks Erik! I really appreciate your help. BTW I liked the idea about just having to double click in the column and getting the date without having to write the "x" but I was counting the number of "x's" later on in a COUNTIF formula to keep a log of how many activities have been performed. I'll see if I can use the date instead and count how many dates appear to do that modification.

Once again thanks for all your help.
 

Forum statistics

Threads
1,141,596
Messages
5,707,303
Members
421,502
Latest member
PULBAG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top