Frequency Due Date Formula

**ainokoori** · 02-23-2011, 09:41 PM

I'm trying to create a conditional formula in excel that will let show the following information:
If the inspection frequency is quarterly (input in cell B2 is QUARTERLY), and the date completed (C2) is 2/24/2011 then the due date (to be imputed into cell D2) is... this is where I want the result of my formula.

I need to make separate formulas for each of the following frequencies:

Daily
Monthly
Quarterly
Semi-Annually
Annually
Bi-Annually
Tri-Annually
5 Years

Not sure if I explained it right. I can see the formula concept in my brain but can't get it to coincide with what excel wants. Thanks for the help!

**rylo** · 02-23-2011, 11:45 PM

Hi

Possibly use CHOOSE based on the position of the selected frequency in the list of frequencies???

rylo

**ainokoori** · 02-24-2011, 05:09 PM

Rylo,

Thank you for the information. I'm still learning a lot about excel, how exactly would I use the CHOOSE function?

**rylo** · 02-24-2011, 05:37 PM

Hi

Put up an example workbook showing your structure, some dummy data, and exactly what formula should be used in each of the given situations.

rylo

**ainokoori** · 02-25-2011, 08:16 PM

What I really want to do with this is have the date you enter in column C automatically calculate a due date in column E based on the frequency entered in column B. I want the STATUS in column D to change between CURRENT and DUE based on the due date as well. I already have the conditional formatting in place to color code the column results appropriately, I just need the formula and where/how to apply it to get the result I want.

Thanks for all of your help so far!

/Holli

**daddylonglegs** · 02-25-2011, 08:39 PM

I made a table in M1:N8, showing the periods and their length in months. You can put the table somewhere else if you want.

The text in the Table should match the text selected so I also made a dropdown list for column B so you can only select those options

Now in E2 copied down I used this formula

=IF(B2="Daily",TODAY(),IF(B2="As Needed","",EDATE(C2,CEILING(DATEDIF(C2,TODAY(),"m"),VLOOKUP(B2,M$2:N$8,2,0)))))

The date returned should always be today or later, is that what you need?

I don't know how you determine whether the status is current or due or something else, what's the rule?

See attached

**ainokoori** · 02-26-2011, 12:58 PM

daddylonglegs,

Thank you so much! That is exactly what I needed for the due date!

For the status, I've put the legend I'm using on a copy of the spreadsheet you sent. I already made a drop-down list for the choices for column D based on your input from the first spreadsheet. I also have conditional formatting already in place to automatically change the color of the cell based on the input.

For the legend, here are some more details:

COMPLETED OR CURRENT - The due date for this item is current (not due)
NO RECORD OF COMPLETION - There is no data available for the start of this inspection item
NOT IN SERVICE - Equipment item to be inspected is not currently in working condition
FAILED - Inspection item failed and will require re-inspection
INSPECTION OR TEST DUE - Due date has been reached or is within 30 days of being due
NEWLY ADDED INSPECTION ITEM - New requirement, will need inspection asap or as required
NOT BUILT YET - inspection item has not been created yet, this is pretty much a placeholder selection

manual input of this from the drop-down list might work best for a few of those items, I mainly want the CURRENT and DUE items to automatically switch based on the due date.

Thank you in advance for all of your help!!

/Holli