IF Formula?

**keith6292** · 10-04-2009, 04:53 PM

I have 3 columns...when both column a and b contain a date i want today's date to appear in column c. Not sure if this requires an IF formula or not. my example is attached. Thanks for any help.Example.xls

**Special-K** · 10-04-2009, 07:50 PM

Try

=IF(LEFT(CELL("format",A1),1)&LEFT(CELL("format",B1),1)="DD",TODAY(),"")

format c as date and copy down the column

**Cheeky Charlie** · 10-05-2009, 05:56 AM

Rubbish title.

Special-K,
1. I don't think you need to specify 1 in left()
2. You can significantly reduce volatility burden of today() by specifying it in one cell then referring to that cell, rather than inserting the function in every iteration

**DonkeyOte** · 10-05-2009, 06:09 AM

CC, the TODAY() point is an interesting one... last April I went along to the MS Excel UserGroup Conference in London specifically to hear Charles Williams' (he of decisionmodels and FastExcel fame) as he was giving a talk on Calculation and I feel there's a lot to learn re: optimisation.

Like you I always assumed it was much better to store TODAY() in one cell and refer to it thereafter, not that this reduces no. of Volatiles (after all a cell refering to a Volatile cell is itself Volatile by association), but simply because it reduces no. of calcs, however CW actually stated that he felt the use of TODAY() etc in each cell was actually insignificant.

There was a thread kicked off over at MrExcel.com sometime ago in which this point was reiterated:

http://www.mrexcel.com/forum/showthread.php?t=371570

more specifically post 4 onwards...

I found it quite an interesting thread.

**zbor** · 10-05-2009, 06:18 AM

What is used to make those calculations?

For example, from link you posted, post 14:

Calculating 5000 =NOW() formulae takes 0.0079 seconds
Calulating 1 =NOW() formula in A1 and 4999 =A1 formula takes 0.0054 seconds

How is calculated?

**DonkeyOte** · 10-05-2009, 06:26 AM

see post 22 ...

**Cheeky Charlie** · 10-05-2009, 08:21 AM

Very interesting!
Based on that, I'd assume that even hard-coding today's date in a separate cell wouldn't really make much difference - what do you think?

I really wanted to go to this year's conference but my employer wouldn't afford me the time, on the basis that "I already know more than enough about Excel". Talk about "in the land of the blind"

CC

**DonkeyOte** · 10-05-2009, 08:32 AM

I think hard coding the date into one cell and then linking to it would make a difference insofar as it would remove the Volatility, how much of an effect would depend of course on the complexity of the linked functions (ie making them non-volatile).

As for the conference - I'm sure those who spoke (Andy P, Bob Phillips) won't mind me saying that given it was the first run some of the content missed the mark and that it will be better this time around... that said I only went to Day 2.

Why ? Well primarily boiled down to the mixed bag of attendees (skill level). Andy & Bob did a dual presentation on Dashboards which was good but Andy ended up getting rushed and Bob's talk on Arrays (which I was looking forward to) fell foul of the "assumed expertise" issue meaning Bob spent far too long having to explain the basics - losing the advanced users quite early and causing the less skilled to start crying... I didn't envy him trying to explain Arrays to beginners in 15 minutes !

That said I still learnt plenty of little tips and tricks, moreover I finally met a few of the guys I've spoken with online over the years.

Hijack over.

EDIT:

Originally Posted by CC

I really wanted to go to this year's conference but my employer wouldn't afford me the time, on the basis that "I already know more than enough about Excel"

Well if they consider spending 2 days in the company of the UK based MS Excel MVP's to be a waste of your time and their money (you being paid to be there) they're obviously shortsighted !