ssblue
asked on
Excel - need to get total based on dates
Not sure the best way to get what I need.
I would like total of hours for dates in the past in column D
I would like total of hours for dates in the future (including current day) in column E
See attachment
Planning.xlsx
I would like total of hours for dates in the past in column D
I would like total of hours for dates in the future (including current day) in column E
See attachment
Planning.xlsx
ASKER
I think we should count the current month as the future.
Here you go.
I just added one more row above your month with their month number:
For Past:
ssblue_Planning.xlsx
I just added one more row above your month with their month number:
For Past:
=SUMPRODUCT(($APJ$5:$APU$5<MONTH(TODAY()))*($APJ7:$APU7))
For Future:=SUMPRODUCT(($APJ$5:$APU$5>=MONTH(TODAY()))*($APJ7:$APU7))
Check in attached in Col APX & APY...ssblue_Planning.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks!
ASKER
How would I adjust this is if I decided that I didn't want the current month in the future.
This would include todays month in the "past".
D6
»bp
D6
=SUM(OFFSET($APJ6,0,0,1,MONTH(TODAY())))
E6
=SUM(OFFSET($APJ6,0,MONTH(TODAY()),1,12-MONTH(TODAY())))
»bp
»bp