Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

Using the TODAY function in an IF - THEN type formula

Posted by Ed West on April 03, 2001 5:00 PM
I've been going crazy trying to figure out the correct way to write a formula for the following situation:

I've created a spreadsheet that helps me keep track of all my open, pending and completed calls regarding technical support for around 100 PC's in my office. There's a column for when the initial request for help was made (Date Entered let's call it A1) a Status column (G1) and a Last Updated Column.

Okay...a call comes in, I type the date in A1. I've got a formula in the Status column (G1) that looks like this
=IF(A1="","","Open") which says, "If A is blank, print nothing, otherwise, print the word "Open". This way I don't have to remember to fill in the Status column when I first enter a request for help.

The Last Updated column (J1) has a formula that looks like this: =IF(A1="","", A1) which simply puts whatever date I've entered for the initial call, but prints nothing if the column is blank.

I'd like to be able to create a formula for the Last Updated column that says, If the Status column contains the word OPEN, print the date in the Date Entered column (column A), if the Status column contains the word "COMPLETED" print the current date (04/03/01)

I thought I could do this with the TODAY( ) command but I keep getting error messages. If this makes ANY sense to anybody and you can decipher what I'm trying to do I'd REALLY like some help. The stupid little paperclip hasn't told me a damn thing. I'd like to bend him into the shape of a middle finger and send it to Bill.


Re: Using the TODAY function in an IF - THEN type formula

Posted by Dave Hawley on April 03, 2001 5:12 PM


Hi Ed

Try this:

=IF(G1="","",IF(G1="Open",A1,IF(G1="Completed",TODAY())))


You should be aware though the TODAY() formula is volatile and will change to a new date each day. You can overcome this with Edit>Copy, Edit>PasteSpecial-Values over the top of itself.

Dave


OzGrid Business Applications


Re: Using the TODAY function in an IF - THEN type formula

Posted by Aladin Akyurek on April 03, 2001 11:10 PM

Hi Ed

My contrib is merely style and contains a question:

Change the formula in G1 to:

=IF(A1,"OPEN","")

The one in J1 to:

=IF(A1,A1,"")

Apparently, you want now in J1:

=IF(G1="OPEN",A1,IF(G1="COMPLETED",TODAY(),""))

but, how do you get COMPLETED in G1, by overwriting the formula it holds?

Aladin


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.