Auto populating due date based on a frequency

JennieK

New Member
Joined
Jul 7, 2020
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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...?
Excel compliance.PNG
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,816
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 7, 2020
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,816
Office Version
  1. 2016
Platform
  1. Windows
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
GHIJKLMNOP
1FrequencyLast Date CompletedNext DueFrequencyMonthsDays
23 yearly07-Jan-2007-Jan-23Weekly07
3Annual07-Oct-1807-Oct-19Fortnightly014
4Annual07-Oct-1907-Oct-20Monthly10
5Annual Bi-Monthly20
6Weekly07-Jul-2014-Jul-20Quarterly30
7Monthly07-Jul-2007-Aug-20Bi-Annual60
8Fortnightly07-Jul-2021-Jul-20Annual120
9Monthly07-Jul-2007-Aug-203 Yearly360
10Bi-Monthly07-Jul-2007-Sep-205 Yearly600
11Quarterly07-Jul-2007-Oct-20
12Bi-Annual07-Jul-2007-Jan-21
13Annual07-Jul-2007-Jul-21
143 Yearly07-Jul-2007-Jul-23
155 Yearly07-Jul-2007-Jul-25
Sheet1
Cell Formulas
RangeFormula
I2:I15I2=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

New Member
Joined
Jul 7, 2020
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,816
Office Version
  1. 2016
Platform
  1. Windows
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
ABC
1FrequencyMonthsDays
2Weekly07
3Fortnightly014
4Monthly10
5Bi-Monthly20
6Quarterly30
7Bi-Annual60
8Annual120
93 Yearly360
105 Yearly600
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

New Member
Joined
Jul 7, 2020
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,816
Office Version
  1. 2016
Platform
  1. Windows
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
GHIJ
1FrequencyLast Date CompletedNext DueStatus
23 yearly07-Jan-2007-Jan-23 
3Annual07-Oct-1807-Oct-19Expired
4Annual07-Oct-1907-Oct-20 
5Annual  
6Weekly07-Jul-2014-Jul-20Expires soon
7Monthly07-Jul-2007-Aug-20 
8Fortnightly07-Jul-2021-Jul-20Expires soon
9Monthly07-Jul-2007-Aug-20 
10Bi-Monthly03-Mar-2003-May-20Expired
11Quarterly24-Apr-2024-Jul-20Expires soon
12Bi-Annual07-Jul-2007-Jan-21 
13Annual07-Jul-2007-Jul-21 
143 Yearly07-Jul-2007-Jul-23 
155 Yearly07-Jul-2007-Jul-25 
Sheet1
Cell Formulas
RangeFormula
I2:I15I2=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:J15J2=IF(H2="","",IF(I2<TODAY(),"Expired",IF(EDATE(I2,-1)<TODAY(),"Expires soon","")))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J2:J15Cell Value="Expires soon"textNO
J2:J15Cell Value="Expired"textNO
 

JennieK

New Member
Joined
Jul 7, 2020
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,816
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,635
Messages
5,676,902
Members
419,657
Latest member
ExcelAl1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top