Excel formula needed

interesting question - I don't know exactly how to do it.

This might help -

http://www.cpearson.com/excel/datetime.htm

Cycloptichorn

ok, cyclo....I've got an idea of how to do it, from reading the microsoft online excel thingie, but I can't seem to get it written correctly.

here's what I'm thinking....

If could be a SUMIF formula, and I could use a dummy column, like column F, which I'd hide. There could be all zeros in column F.

I could say, "add B1 and F1, IF the date in A1 is greater than 12/31/09 and less than 1/1/11"

Or, to avoid making a column F, how would you say "=B1 IF the date in A1 is grater than 12/31/09 or less than 1/1/11"

Try this.

=IF(YEAR(A1)=2010,B1,"")

Put that formula in cell C1...or if you put it in another row, change your row numbers.

The format in column A must be in a date format. I can help you convert those if you need help with that too.


I'm sort of an excel guru.

I have to figure out the Excel syntax to make it do it. I did most of my spreadsheet work in Lotus 123 when I was working in accounting and have to translate what I would have done in Lotus to Excel's syntax. So far, I haven't yet gotten it right in the sample sheet I'm testing it on.

This is the logic you want:

If (Column $ARow? is greater than 1/1/2010)$B1

The dollar symbols are absolute values for the column while the question mark symbol represents the row value for the current row containing the formula. I think that Excel automatically converts the date to a numeric value and can do the simple calculation to see if column a is greater than a specific date in the formula.

I'll work on it a bit more and see if I can get it to work in my spreadsheet and give you the exact formula.

Yep, that looks good. Let me see if it works in the test sheet.

Oh Man!

How simple was that!

Thanks maporsche.

Assuming column A is the date, then:

=IF(A1-"1/1/2010">=0,B1,"")

ok, show's over.

move along now.

Of course, maporche's is simpler.

No problem.

You could make my answer the "Selected Answer" and then I can join the ranks of the 'helpful' people of A2K.

I too hope to walk amongst the excel gods one day.

For now, I am only excel geek, not excel guru.

Ooh, sorry I missed this one. Nice job, all.

Word.

Haha! She made your begging for a "selected answer" ribbon the selected answer!

<mopes due to not having a selected answer ribbon>

Haha...I'll take what I can get.

Am I the only one who got that?

Office humor?

excel-lent...

Accessssssssss-zactly.