help with YTD budget amount

[jtyme9]

So I'm trying to figure out a formula to generate a Year-to-Date amount for my personal budget. So for example, I have $1,200 budgeted for gas (annually). Next to that, I have what my YTD gas expenses are (lets say they are $600 as of 5/16/13). I want to add a formula that takes that 1,200 and generates the YTD amount of that budget.

Mathmatically I would do this as:

Annual Budget * (# of days passed this year / 365days) = YTD budget amount
$1200 * (135 days passed in 2013 / 365 days) = 443.84



I want the formula to know what date it is an continually self update.

So maybe the formula would be something like:

=sum(1200* ( (today - 1/1/13)/365 ) )

Just need to find a what to make that work.

[benishiryo]

hi jtyme9, welcome to the forum. say annual budget in A1, then:
=(DATE(YEAR(TODAY())+1,1,1)-TODAY())/(DATE(YEAR(TODAY())+1,1,1)-DATE(YEAR(TODAY()),1,1))

i used 1-jan-2014 to minus today's date because i would consider yesterday & today as 2 days. if you consider it as 1, then:
=(DATE(YEAR(TODAY()),12,31)-TODAY())/(DATE(YEAR(TODAY()),12,31)-DATE(YEAR(TODAY()),1,1))

[jtyme9]

Thanks so much! I had to do a slight modification, but it worked!


say A1 is the annual budget amount:
=A1*(1-(DATE(YEAR(TODAY())+1,1,1)-TODAY())/(DATE(YEAR(TODAY())+1,1,1)-DATE(YEAR(TODAY()),1,1)))

I also had to add the "1-" at the beginning. What your formula did was figure how many days are left in the year, when i was looking to see how many had passed.

But, i got a working formula and that's all I care about. Thanks a bunch!!