help with formula please

[dido22]

Hello.

I am developing a workbook to track my finances.

One sheet has a list of all my expected bills with their start dates. Some
bills are quarterly, eg telephone. Before adding/subtractiong these to my
current account I need to test that today's date is 3,6,9,12,15 etc etc
months after the start date. I know how to use IF functions, but the formula
I am getting is very messy. Is there an elegant way of doing this?

Thanks for all ideas, I use excel 2007

KK

 

[Ron Rosenfeld]

Hello.

I am developing a workbook to track my finances.

One sheet has a list of all my expected bills with their start dates. Some
bills are quarterly, eg telephone. Before adding/subtractiong these to my
current account I need to test that today's date is 3,6,9,12,15 etc etc
months after the start date. I know how to use IF functions, but the formula
I am getting is very messy. Is there an elegant way of doing this?

Thanks for all ideas, I use excel 2007

KK

Hard to tell exactly what you want, but perhaps you can use the EDATE function.

Please give some examples of various data and desired output of formula.

 

[joeu2004]

One sheet has a list of all my expected bills with their
start dates. Some bills are quarterly, eg telephone. Before
adding/subtractiong these to my current account I need to
test that today's date is 3,6,9,12,15 etc etc months after
the start date.

If the start date is in A1, EDATE(A1,3), EDATE(A1,6), etc calculate the
quarterly anniversary dates. But more generally, you might try:

=IF(MOD(DATEDIF(A1,TODAY(),"m"),3)=0,"quarterly","not quarterly")

Note: Instead of using TODAY(), I would assign a cell to "today's date" and
reference it in the formula. Usually, we do not literally mean "today".

Caveat: DATEDIF(...,"m") works fine for start dates on day 1 through 28 of
all months. Anomalies arise if the start date is day 28 through 31 and the
anniversary month is Feb. Anomalies also arise if the start date is day 31
and the anniversary month is Apr, Jun, Sep or Nov. These anomalies can be
addressed, but it makes the formula much more messy. Do you need a solution
that hands start dates on days 29 through 31 as well?

 

[dido22]

Thanks, this helps nicely. KK

If the start date is in A1, EDATE(A1,3), EDATE(A1,6), etc calculate the
quarterly anniversary dates. But more generally, you might try:

=IF(MOD(DATEDIF(A1,TODAY(),"m"),3)=0,"quarterly","not quarterly")

Note: Instead of using TODAY(), I would assign a cell to "today's date"
and reference it in the formula. Usually, we do not literally mean
"today".

Caveat: DATEDIF(...,"m") works fine for start dates on day 1 through 28
of all months. Anomalies arise if the start date is day 28 through 31 and
the anniversary month is Feb. Anomalies also arise if the start date is
day 31 and the anniversary month is Apr, Jun, Sep or Nov. These anomalies
can be addressed, but it makes the formula much more messy. Do you need a
solution that hands start dates on days 29 through 31 as well?