Excel IF function (with date)

FreddieFrugal

Hello I'm making a sreadsheet to use for budgeting.

I've used an IF function so that if the date next to the cell is the same as a specific date that I've entered into a separate cell then it changes the calculation values.

Just simple IF(D2=A12,500,0) or that sort of thing.

However I also want to use an IF function for a date but just based on the month.

As in if the month of the date in cell D2 is May or 05.

However I haven't managed to figure out how to do it.

Basically I want it so that whenever it is a particular month (regardless of the year), the calculation changes. However it needs to base this off the full date in the adjacent cell. Is there a way of getting it to read the month ignoring the year.

(It's complicated because I've got the dates in the spreadsheet set to change whenever I input the 'current date' into a cell, so the formulas need to be able to adapt to the changes and still do the correct calculations for the dates)

esuhl

It's been a while, but isn't there a month() function that would take care of that?

Le_Kirk

Have a look at the first link: -
https://support.office.com/en-us/article/Date-and-time-functions-reference-fd1b5961-c1ae-4677-be58-074152f97b81
and then the second. It is complicated but if you start with the date calculation first, make sure that works and then build the rest of your calculation round it.
https://support.office.com/en-us/article/Calculate-the-difference-between-two-dates-8235e7c9-b430-44ca-9425-46100a162f38

System

=IF(MONTH(D2)=5,<true value>,<False value>)

This works if D2 gets its date from another cell I.E. D2 content is something like '=A4')

FreddieFrugal

Yes that works, ha! Thanks for the quick responses. I was actually just popping back on to say I found it myself on a webpage somewhere in the end.

formula in the end to make this whole thing work is:

=IF(D2>=$A$26,($A$2*($A$16/12))+($A$2-$A$6)-($A$22+(IF(MONTH(D2)=$A$12,$A$10,0))),($A$2*($A$8/12))+($A$2-$A$4)-($A$14+(IF(MONTH(D2)=$A$12,$A$10,0))))

:eek:

But yes it's using IF(MONTH(D1)=3,5,0)

meaning If the month in cell D1 is 3 (March) then use value 5, if not use 0

But I've used it like IF(MONTH(D1)=E5,5,0)

So that I can alter the month that it's looking for if it changes in the future, by altering the number in cell E5

esuhl

FreddieFrugal wrote: »

formula in the end to make this whole thing work is:

=IF(D2>=$A$26,($A$2*($A$16/12))+($A$2-$A$6)-($A$22+(IF(MONTH(D2)=$A$12,$A$10,0))),($A$2*($A$8/12))+($A$2-$A$4)-($A$14+(IF(MONTH(D2)=$A$12,$A$10,0))))

:eek:

If you end up with complicated formulae that might be hard to debug, it's sometimes worth breaking up the problem into smaller chunks and using multiple columns showing your "working out", rather than one column that magically calculates the final answer. You can hide the columns that only show the "working out". Just a thought...

It's very easy to run out of space with formulae, as you can only use 255 characters (unless MS have fixed that in later versions of Excel).

You can also name ranges of cells; sometimes it helps readability if you use range names rather than cell references.