• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a volume of audits

C

CEK2013

New Member
Hi all,

I am looking for help to create a formula that provides dates in monthly intervals. For example if a cell says Compliant then I need it to display a date 6 months in the future and then another date 6 months on?

I have used COUNTIFS to cover this for the first 6 month visit but I am struggling to find a formula to create multiple dates?

Please can someone help!
 
Hi CEK,
I'm missing a bit of context/information. For example: is it a date 6 months from today or from a fixed date? I have no clue why you are using COUNTIFS..

A sample file + manual expected results would greatly help.

To start off, here is an idea:
Code: 
=DATE(YEAR(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))
 
Hi Xiq,

It is a fixed date that changes each year. I used COUNTIFS orginally as we were only looking for the next audit date, but now the brief is to chart a frequency of dates to conduct visits!

I have copied an example below:

Inspection date Report Status Overall Compliance QA Manager Current Visits as per Risk Profile Nov-13 Dec-13 Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14
13/08/2013 Current Compliant 2
03/04/2013 Current Minor 4
17/10/2013 Current Moderate 12
Total audits required by month
What I need is for the overall compliance to trigger the number of visits and that number of visits to be spread equally across the 12 months depending on the overall compliance?

Hope that make sense?
 
Would you need the level of compliance to trigger a follow-up audit six months after the previous, or would it be in a specific future month?
 
Hi Cek,

No, it does not make sense and you're example doesn't show what results you would expect. That makes me sad :(

Based on what did (sort of) make sense, I made a table. Please check attached file.
 

Attachments

Yes it would need to be 6 months in the future, not on a specific month.

Thanks Xiq, that looks just the job, how do I replicate that for the 2 rows below based on a higher frequency of visits?

Thanks
 
Great thanks Xiq,

If the date in column E or the compliance level in Column G change will that forumla adapt?

Thanks
 
Hi Cek,

There is no date in column E or compliance in column G...
Though, it should adapt if you change the date of "Inspection date" and/or the frequency of visits. You could (and should) test it yourself. Keep in mind though, the formula is limited up to 12 months into the future (according to your request).
 
Hi Xiq,

I have changed the inspection date in Column A and the frequency in D and the cells don't change. What I imagine is that all that needs to be done is the date changed in A and the Overall Compliance in C, the rest of the s/sheet is automatically populated.

Therefore column D needs to change when Column C does, e.g if row 2 is no longer compliant but minor then Column D needs to change from 2 to 4 visits and this to be populated in the 12 months ahead?
 
CEK2013 said:
I have changed the inspection date in Column A and the frequency in D and the cells don't change.
Click to expand...
Try to recalculate (F9), maybe the workbook is set to manual calculation.

CEK2013 said:
if row 2 is no longer compliant but minor then Column D needs to change from 2 to 4 visits and this to be populated in the 12 months ahead?
Click to expand...
Try the following formula, place it in D2 and copy down.
Code: 
=LOOKUP($C2,{"Compliant","Minor","Moderate"},{2,4,12})
 
Hi Qiq,

When I try to build the s/sheet around your example it says there are extra rows inserted? And then the value in the month is not recognised when i go to save the file?
 
Hi CEK,

You haven't told us what the problem is. But I guess it's about the "keep in mind" warning I gave you. Correct?

Xiq said:
Keep in mind though, the formula is limited up to 12 months into the future (according to your request).
Click to expand...
 
The cells highlighted in yellow in Column I are not showing the correct number of audits across the range of Column J to U, is there a way to ensure that whatever the date in column E states it can always pick up the next 12 months of activity?
 
Hi CEK,

Question: if the "inspection date" is 1 November 2013, should it plan an inspection for November 2013?
If not, then here is a modification (see attached file). It only looks at months.
 

Attachments

Cool,

If you want to move around the formula, then:
J$1 = the column month + year at the top of the column (i.e.: 13-Nov)
$E2 = the "Inspection date"
$I2 = the "Current Visits as per Risk Profile"
$A$1 = $A$1 (don't change it!!!!)

=IF(EOMONTH(RIGHT(J$1,3)&20&LEFT(J$1,2),0)>EOMONTH($E2,0),SUMPRODUCT(--(RIGHT(J$1,3)=TEXT(EOMONTH($E2,ROW(OFFSET($A$1,,,$I2))*12/$I2),"mmm"))),)
 
You must log in or register to reply here.
Back
Top