SUMIF Statements aren't working for me

[jonco]

I'm trying to figure out why these two statements don't always work.


=SUMIF(D35000,TODAY(),C3:C5000)

=SUMIF(D35000,(TODAY()-1),C3:C5000)

I'm copying date data into a spreadsheet with the date in column D.

I then use the following statement in a macro to format the date as a date
(just to be sure it's useable as a date)
Selection.NumberFormat = "mm/dd/yy"
My goal is to add the teansaction amounts in column C that were done
today... and yesterday and post them into cells.
(If I physically type over the pasted date it seems to add correctly...so
I'm thinking it's formatting...but not sure how to get it to do it in the
formula or in a macro.
Any help you can give would be appreciated.

Jonco

 

[Dave Peterson]

It sounds like your data is coming in as Text. Formatting the range isn't
enough (as you've seen). You actually need to change the value to a date.

One way is to select that column and do Data|text to columns. Choose the
correct date format for the data (mdy???).

Or you may be able to just select that range and do
edit|replace
what: / (slash)
with: /
replace all

Excel will see the values in those cells as dates.

(Record a macro to get the code--but try it manually to see if the formula
updates correctly.)