Year-to-date formula?

[ftjjk]

I found the following formula for creating a year to date formula fro
http://www.beyondtechnology.com/tips.shtml

=SUM(OFFSET(A2,0,0,1,MONTH(NOW())))

My problem is that my data is not one cell per month but is the actua
date input in column A (my data is vertical not horizontal)and ca
range from 3 to 9 days of inputs per month. Rather than use the mont
function I need to use the input dates in column A. I was considerin
an IF statement to compare A(x) to NOW() but I am have serious brai
drain and can't go anywhere with this. Any help would be appreciated

 

[Jeff]

Hi.

I'm not sure exactly what you're after, but if you
might use the following formulas:

For number of days from date in A1 from 1/1/curYR:
=A1-DATE(YEAR(NOW()),1,1)

for number of months from date in A1 from 1/1/curYR:
=(YEARFRAC(A1,DATE(YEAR(NOW()),1,1),))*12
(you can use the fraction or round or truncate it)

LOL!

 

[Guest]

This may be what you're looking for

You would use an array formula. Suppose your dates are in column A (rows 2-54) and the values you want summed year-to-date are in column B

Try =SUM(IF(YEAR($A$2:$A$54)=YEAR(NOW()),IF($A$2:$A$54<=NOW(),$B$2:$B$54,0),0)

Since this is an array formula, you would need to type it and then use ctrl-shift-enter, instead of just enter. If you do that you will see curly brackets around your formula

Good luck

Art

 

[Charlie]

Art
This is neat, it is something I have been look for. It
sure beats the Running sum I've been using.

Charlie O'Neill

-----Original Message-----
This may be what you're looking for:

You would use an array formula. Suppose your dates are

in column A (rows 2-54) and the values you want summed
year-to-date are in column B.

Try =SUM(IF(YEAR($A$2:$A$54)=YEAR(NOW()),IF ($A$2:$A$54<=NOW(),$B$2:$B$54,0),0))

Since this is an array formula, you would need to type it

and then use ctrl-shift-enter, instead of just enter. If
you do that you will see curly brackets around your
formula.