Auto populating due date based on a frequency

[JennieK]

Help needed please, I am working on a new compliance sheet and would like to auto populate the due date (Column I) based on the last date completed (Column H) based on frequency of the service needed (Column G)
I cannot figure this out and would appreciate if someone could show me how...?

 

[Toadstool]

Hi JennieK,

A few questions:

1. Do you need to avoid weekends/holidays for the Next Due date?
2. Does 3 yearly mean 3 times a year or every 3 years?
3. Are 3 yearly, Annual and Weekly the only Frequency options?

 

[JennieK]

Hi Toadstool,

Apologies, clarity below
1) No
2) it means every 3 years
3) these are the frequencies I need to use:

Weekly

Fortnightly

Monthly

Bi-Monthly

Quarterly

Bi-Annual

Annual

3 Yearly

5 Yearly

 

[Toadstool]

JennieK,

Here I created a table for each Frequency option showing how many months or days to add. EDATE will add months for the matching Frequency and the date is held as days since 1/1/1900 so just adding the days for the matching Frequency will work.

JennieK.xlsx
	G	H	I	J	K	L	M	N	O	P
1	Frequency	Last Date Completed	Next Due					Frequency	Months	Days
2	3 yearly	07-Jan-20	07-Jan-23					Weekly	0	7
3	Annual	07-Oct-18	07-Oct-19					Fortnightly	0	14
4	Annual	07-Oct-19	07-Oct-20					Monthly	1	0
5	Annual							Bi-Monthly	2	0
6	Weekly	07-Jul-20	14-Jul-20					Quarterly	3	0
7	Monthly	07-Jul-20	07-Aug-20					Bi-Annual	6	0
8	Fortnightly	07-Jul-20	21-Jul-20					Annual	12	0
9	Monthly	07-Jul-20	07-Aug-20					3 Yearly	36	0
10	Bi-Monthly	07-Jul-20	07-Sep-20					5 Yearly	60	0
11	Quarterly	07-Jul-20	07-Oct-20
12	Bi-Annual	07-Jul-20	07-Jan-21
13	Annual	07-Jul-20	07-Jul-21
14	3 Yearly	07-Jul-20	07-Jul-23
15	5 Yearly	07-Jul-20	07-Jul-25
Sheet1

Cell Formulas
Range	Formula
I2:I15	I2	=IF(H2="","",EDATE(H2,INDEX($O$2:$O$10,MATCH(G2,$N$2:$N$10,0)))+INDEX($P$2:$P$10,MATCH(G2,$N$2:$N$10,0)))

 

[JennieK]

Thank you, I have tried this on my sheet entering this formula in I2 but nothing appears but N/A.....?
Do I need to add anything to this formula please?

 

[Toadstool]

You need to have the table from N1 to P10 in the sheet.

If those cells are in use on your spreadsheet then give me a 3 by 10 area which is unused... or you can put it on another tab like this:

JennieK.xlsx
	A	B	C
1	Frequency	Months	Days
2	Weekly	0	7
3	Fortnightly	0	14
4	Monthly	1	0
5	Bi-Monthly	2	0
6	Quarterly	3	0
7	Bi-Annual	6	0
8	Annual	12	0
9	3 Yearly	36	0
10	5 Yearly	60	0
FreqTable

...and your formula in I2 becomes:
=IF(H2="","",EDATE(H2,INDEX(FreqTable!$B$2:$B$10,MATCH(G2,FreqTable!$A$2:$A$10,0)))+INDEX(FreqTable!$C$2:$C$10,MATCH(G2,FreqTable!$A$2:$A$10,0)))

 

[JennieK]

oh I see now! fantastic thank you so much. Now I am going to put conditioning formatting into the column J to show In date, expire in 1 month or Expire any help with this?
I wish I could hold this all in my head like you have

 

[Toadstool]

I'd suggest "In date" just be empty in column J so they don't confuse the more important statuses.

You could put the calculations in the Conditional Format but as you're using J to show the status then just check for the resulting text.

Here's column J and the Conditional Formats required.

JennieK.xlsx
	G	H	I	J
1	Frequency	Last Date Completed	Next Due	Status
2	3 yearly	07-Jan-20	07-Jan-23
3	Annual	07-Oct-18	07-Oct-19	Expired
4	Annual	07-Oct-19	07-Oct-20
5	Annual
6	Weekly	07-Jul-20	14-Jul-20	Expires soon
7	Monthly	07-Jul-20	07-Aug-20
8	Fortnightly	07-Jul-20	21-Jul-20	Expires soon
9	Monthly	07-Jul-20	07-Aug-20
10	Bi-Monthly	03-Mar-20	03-May-20	Expired
11	Quarterly	24-Apr-20	24-Jul-20	Expires soon
12	Bi-Annual	07-Jul-20	07-Jan-21
13	Annual	07-Jul-20	07-Jul-21
14	3 Yearly	07-Jul-20	07-Jul-23
15	5 Yearly	07-Jul-20	07-Jul-25
Sheet1

Cell Formulas
Range	Formula
I2:I15	I2	=IF(H2="","",EDATE(H2,INDEX(FreqTable!$B$2:$B$10,MATCH(G2,FreqTable!$A$2:$A$10,0)))+INDEX(FreqTable!$C$2:$C$10,MATCH(G2,FreqTable!$A$2:$A$10,0)))
J2:J15	J2	=IF(H2="","",IF(I2<TODAY(),"Expired",IF(EDATE(I2,-1)<TODAY(),"Expires soon","")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J15	Cell Value	="Expires soon"	text	NO
J2:J15	Cell Value	="Expired"	text	NO

 

[JennieK]

Ok that makes more sense than what I wanted originally but in my sheet the test in column J is only coming as black and not colour coded?

 

[Toadstool]

So the text is appearing but in black...

Here's how to set the colour coding:

1. Select the cells to format.
2. Select Conditional Formatting, New Rule, Format only cells that contain.
3. Select Equal to and type in the text Expired.
4. Select Format and then Font or Fill where you choose how you want them formatted.
5. Select OK and OK again.
6. Repeat steps 1 through 5 for the second condition Expires soon and a different format font and/or fill.

This video explains in detail:
How to set Conditional Format