Need Help Fixing a Formula in an Excel Spreadsheet

Question

Need Help Fixing a Formula in an Excel Spreadsheet

Kiwi1 40

I have a spreadsheet created, but the formula in on column is causing a problem. I need to keep track of when children in my childcare center's physical expires. My columns are:

Column A = Child's Name

Column B = Child's DOB

Column C = Health Appraisal Date

Column D = Child's Current Age

Column E = Health Appraisal Expiration Date

Column F = Status (expired, current or expiring in 30 days)

Column G = Child's Age at the time of the health Appraisal

Here's are examples of some of the rows of data starting with column B ( I excluded the child's name column (A).

User's image

The problem I am having is with Column E. It is essentially calculating right, but I need it to do it a little different. The current formula is calculating this data- Health appraisals expire every 13 months for infants - 2 1/2 year olds. Health appraisals expire every 2 years for children 2 1/2 years old - schoolage. I need the formula to calculate so that as soon as they become a preschooler (2 1/2 - schoolage) they wouldn't need their health appraisal 13 months from the last date, but instead 2 years from the last health appraisal date. If you look at the chart the one that says expired is technically not expired. The child had their last health appraisal on 3/14/25 (when they were 2 years, 3 months old), but they are over 2 1/2 and are now considered a preschooler so their health appraisal isn't technically due until 3/14/2027. The other two rows are correct, but the third row will end up being wrong if I don't get the formula corrected because they will turn 2 1/2 before their current health appraisal will expire so it wouldn't be due until 6/23/28. I hope this makes sense. A Child under 2 1/2 expires 13 months and children who reach 2 1/2 or are already over 2 1/2 expire every two years. Is there a formula that can do this? Here's the current formula that's being used in column E and column F.

Column E- Health Appraisal Expiration Date

=IF(C3="","",EDATE(C3,IF(DATEDIF(B3,C3,"M")<30,13,24)))

Column F- Status (expired, current or expiring in 30 days)

=IF(E3="","",IF(E3<TODAY(),"Expired",IF(E3<=TODAY()+30,"Expiring in 30 days","Current")))

Thank you, in advance, for your help!

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Use a formula in Column E that checks both conditions:

if the child was already 30 months or older on the appraisal date, expire in 24 months
if the child was under 30 months on the appraisal date, but will reach 30 months before the 13-month expiration, then also expire in 24 months
otherwise expire in 13 months

In E3:

=IF(C3="","",IF(DATEDIF(B3,C3,"M")>=30,EDATE(C3,24),IF(EDATE(B3,30)<=EDATE(C3,13),EDATE(C3,24),EDATE(C3,13))))

How it works:

DATEDIF(B3,C3,"M")>=30 checks whether the child was already 2 years 6 months old at the time of the health appraisal.
EDATE(B3,30) calculates the date the child turns 30 months.
EDATE(C3,13) calculates the normal 13-month expiration date.
If the 30-month birthday is on or before that 13-month expiration date, the formula switches to 24 months from the appraisal date.

For the examples described:

appraisal at 2 years 3 months and now over 2 1/2: this formula returns 2 years from the appraisal date
child currently under 2 1/2 but turning 2 1/2 before the 13-month mark: this formula also returns 2 years from the appraisal date
child still under 2 1/2 through the full 13 months: this formula keeps the 13-month expiration

Column F can stay as-is:

=IF(E3="","",IF(E3<TODAY(),"Expired",IF(E3<=TODAY()+30,"Expiring in 30 days","Current")))

If Column E shows a number instead of a date, format the cells as a date:

Home > Number Format > Short Date