Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
help with formula please
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
help with formula please
On Mon, 20 Feb 2012 12:33:27 -0000, "dido22" wrote:
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
help with formula please
"dido22" wrote:
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
help with formula please ......... Thanks
Thanks, this helps nicely. KK
"joeu2004" wrote in message ... "dido22" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |