Excel Formula: Help With IF and Current Date

mmcconaghy January 18, 2012 at 19:16:35

In cell B1 enter the formula: =IF(OR(A1=1,A1="PARTIAL"),TODAY(),"") Be advised, that by using the TODAY() function, the date will change to the current date every time you open the sheet. So today B1 will show 01/18/2012 but tomorrow it will show 01/19/2012 MIKE http://www.skeptic.com/

Thank you!! that is very helpful ... but what if I didn't want the date to change? Is that possible?

DerbyDad03 January 18, 2012 at 19:29:01

SpanishSoul, I'd like to offer a posting tip: When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "Excel formula help" we wouldn't be able to tell one question from another and the Archives would essentially be useless. I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used. Thanks! DerbyDad03 Office Forum Moderator Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03 January 18, 2012 at 19:34:33

re: If I type a "1" and/or "PARTIAL" "1" or "Partial" makes sense, but if you really plan to enter "1" and "Partial" in the cell, then you'll need something a bit more complex than Mike's suggestion. Is "1 Partial" or "Partial 1" something that you might enter? Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Nope Mike pretty much got it (I tried the formula and it works perfectly) ... Although, the thing about the date changing from day to day is a bit of a bummer ... :/

DerbyDad03 January 18, 2012 at 19:54:19

If you don't want the date to change, you'll have to use a macro. 1 - Right click the sheet tab for the sheet you want this happen in and choose View Code. 2 - Paste this code into the pane that opens. 3 - Enter 1 or Partial in A1. The current date will be hard coded into B1. 4 - Clear A1. B1 will clear itself. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then If Target = "" Then Range("B1") = "" If Target = 1 Or Target = "Partial" Then _ Range("B1") = Date End If End Sub Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank You Mike and Thank You DerbyDad03 :) ... I tried both of your codes and they seem to work. But I just tried the one that you gave me DerbyDad03 and hopefuly I did it right and it works out for me! You guys where great and quick to answer. I appreciate it. Thanks a bunch!!!!

DerbyDad03 ... The formula didn't work. I checked again today and the date changed maybe I did something wrong. idk ... :/

DerbyDad03 January 20, 2012 at 09:24:39

What I offered is not a formula. It can't be entered in a cell. It is what as known as "Worksheet Change Macro" Since it hardcodes a date into B1 (e.g. 1/20/2012) just as if you typed it in, it will not/can change unless you make a change to A1 and that change results in either 1 or Partial. At that point, the current date will be re-written into B1. After you enter 1 or Partial in A1, click on B1. You should see a hardcoded date, assuming you followed the instructions included with the code in my previous post. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I know ... I think I know what I did wrong. I didn't change the cells. But how would I do more than one cell. Would I have to re-write that whole code over for each set of cells that I want it to apply too?

DerbyDad03 January 20, 2012 at 17:08:59

No, the code could be applied across a range. For example this version will work for any change in Column A, putting the date in the corresponding cell in Column B. If you need something different, then you'll need to supply some specifics. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then If Target = "" Then Range("B" & Target.Row) = "" If Target = 1 Or Target = "Partial" Then _ Range("B" & Target.Row) = Date End If End Sub Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Why would the target column equal 1 and not A? Wouldn't it have to be A? and another stupid question because I know most of my questions have been but ... If I use the code that DerbyDad03 is giving me ... Then I don't have to use Mikes Formula, right? It's just the code and not the formula ... or is it both together?

DerbyDad03 January 21, 2012 at 11:55:51

re: Why would the target column equal 1 and not A? Wouldn't it have to be A? Did you try the code? If it had to be A, it wouldn't work as I wrote it, would it? However, since it does indeed work, then obviously it doesn't have to be A. Counting from left to right, what number Column is Column A? The syntax I used is the correct syntax for VBA code. re: It's just the code and not the formula ... or is it both together? The formula that Mike gave you will put the TODAY() function in B1. As we know, the problem (in this case) with the TODAY() function is that it updates every day. Even if you put Mike's formula in a cell, as soon as the Worksheet_Change Macro runs, the formula will be overwritten by the date. As I've said a few times, the macro hard codes the date into the cell, just as if a user had entered it via the keyboard. In other words, it's going to overwrite whatever is in the cell. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I was just trying to understand why it was a 1. So, that if I wanted to change it from the A and B column to the F and G column I would be able to. Since I only used the A and B column as examples. They aren't the real cells I'm working with but right now I don't have access to the spreadsheet I'm working with since it's at work. So, I can't tell you the real columns I'm working with.

DerbyDad03 January 21, 2012 at 20:30:53

The code needs a number to represent the column - not a letter - when it checks for the change. Just count from left to right to determine the column number that you need. If you are referring to a column inside the parenthesis where you define a Range then you use the column letter enclosed with quotes. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.