Excel Formula: Help With IF and Current Date

January 18, 2012 at 18:56:13
Specs: Mac OS X 10.6.6
I want to create an excel spreadsheet where ...

If I type a "1" and/or "PARTIAL" in cell A1 then cell B1 will autofill with the current date but if there is nothing in cell A1 then cell B1 will be blank as well.

I hope I got my point across. I've been asking around and trying to google it but I haven't been able to find anything. If any of you know how to do this that would be great :)


See More: Excel Formula: Help With IF and Current Date

Report •

#1
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/


Report •

#2
January 18, 2012 at 19:28:04
Thank you!! that is very helpful ... but what if I didn't want the date to change? Is that possible?

Report •

#3
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.


Report •

Related Solutions

#4
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.


Report •

#5
January 18, 2012 at 19:40:19
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 ... :/

Report •

#6
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.


Report •

#7
January 19, 2012 at 10:16:59
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!!!!


Report •

#8
January 20, 2012 at 08:04:11
DerbyDad03 ... The formula didn't work. I checked again today and the date changed maybe I did something wrong. idk ... :/

Report •

#9
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.


Report •

#10
January 20, 2012 at 16:50:01
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?


Report •

#11
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.


Report •

#12
January 21, 2012 at 03:24:15
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?


Report •

#13
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.


Report •

#14
January 21, 2012 at 14:16:36
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.


Report •

#15
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.


Report •

Ask Question