What formula can one use to Count dates?

[Digital2k]

Here's the Question:
I want to know what I will get paid TODAY, THISWEEK(Monday-Friday) or by the
MONTH.
I know how to get what I will get paid today:
In cell C2 =IF(A2=TODAY(),B2,).
I don't know how to get the Week (D2) and Month.(E2)
Please help.
Digital2k

A B C D E F
1 Dates Amount Today This This
Week Month
2 6/27/06 $500 0 $700 $700
3 6/28/06 $200 $200 $700 $700
4 7/1/06 $600 0 0 $700
_________________________________
5 Total $1300 $200 $700 $700

 

[Digital2k]

P.S. If there is a better way to set this spread sheet up to get the
results, please advise.
Thanks,

 

[Bob Phillips]

Today

=SUMIF(A:A,TODAY(),B:B)

This week

=SUMIF(A:A,">="&TODAY()-WEEKDAY(TODAY(),2)+1,B:B)-
SUMIF(A:A,">="&TODAY()-WEEKDAY(TODAY(),2)+8,B:B)

This month

=SUMPRODUCT(--(MONTH(A3:A20)=MONTH(TODAY())),B3:B20)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Digital2k]

Thank you very much!
However on the week calculation if I enter 7/2/06 it counts it as part of
this week.
I'm sure it is a minor adjustment. can you or anyone help?
Thanks
Digital2k

 

[Peo Sjoblom]

I apologize for a future post, I just tested the formula by changing windows
date and then forgot to change it back

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Dave Peterson]

I've always said that you are a man ahead of your time.

 

[Digital2k]

Thank you, That did it!
Digital2k

I apologize for a future post, I just tested the formula by changing
windows date and then forgot to change it back

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Bob Phillips]

You said Mon-Fri in the original post. 2nd July is a Sunday.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Peo Sjoblom]

Try

=SUMIF(A:A,">="&TODAY()-WEEKDAY(TODAY(),2),B:B)-SUMIF(A:A,">="&TODAY()-WEEKDAY(TODAY(),2)+7,B:B)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey