Excel, IFs and Dates

I have a household budget, it is split into fixed and variable expenses. I would like to include the fixed expenses only during/after the month they are incurred.

Not a problem using the following lovely formula (using January as an example):

=IF((MONTH(TODAY()))>=1,Jan!A15,0)

[Where A15 = my fixed costs for the month].

But, the problem is that if I now go back and look at my 2014 budget all the fixed expenses have gone back to zero, as we’re now in January (or later) again.

What I need to do is tell the formula, that IF it’s later than Jan 2015 (or any given year) then use A15 otherwise 0, but I don’t know how to do it. Can anyone help?

David Henson

Posted 2015-02-04T11:31:03.530

Reputation: 19

Don't post your question in multiple places, please. http://stackoverflow.com/questions/28320221/if-statements-date-and-excel/28320614#28320614

– CallumDA – 2015-02-04T14:50:19.620

Answers

Consider:

=IF((TODAY()>DATE(2015,1,31)),Jan!A15,0)

After all, if its later than Jan, its later than 31 January 2015

Gary's Student

Posted 2015-02-04T11:31:03.530

Reputation: 15 540

Thanks - This solved the problem. It's been upsetting me for a very long time. – David Henson – 2015-02-04T13:43:45.027

@DavidHenson You are quite welcome! – Gary's Student – 2015-02-04T13:44:46.063

Not sure but maybe:

=IF(AND(YEAR(TODAY())>2014,MONTH(TODAY())>=1),Jan!A15,0)

pnuts

Posted 2015-02-04T11:31:03.530

Reputation: 5 716

You're not sure? – Chenmunka – 2015-02-04T13:42:28.120

If you want it to be for every year, it would be nice to not have to know which are leap years. If you use 0 for the day in the DATE function, it uses the last day of the previous month. You can even use a month 13 and Excel treats it as January of the next year.

=IF(TODAY()>DATE(2015,2,0),Jan!A15,0)
=IF(TODAY()>DATE(2015,3,0),Feb!A15,0)
=IF(TODAY()>DATE(2015,4,0),Mar!A15,0)
...
=IF(TODAY()>DATE(2015,13,0),Dec!A15,0)

Additionally, it'd be nice if you didn't have to hard-code the year values. I don't know how your sheet is setup, but, for an example, let's assume that column B has some date in January 2015. In that case, you can use

=IF(TODAY()>DATE(YEAR(Jan!B15),MONTH(Jan!B15)+1,0),Jan!A15,0)

Engineer Toast

Posted 2015-02-04T11:31:03.530

Reputation: 3 019

Wow. Thanks that's taking things to the next level - I might give it a try. – David Henson – 2015-02-05T12:22:31.653