Help Creating an excel spreadsheet

Question

I need help creating an excel spreadsheet that will allow me to write a students name, date of birth, current health appraisal date and then a formula that will calculate when their health appraisal expires. The formula for infant-2 1/2 years old will expire after 13 months from last appraisal date and the formula for children 2/12-schoolage will expire after 2 years. I don't now what formulas to put in D, E and F cells?

Column A

Child's Name

Column B

Date of Birth

Column C

Health Appraisal Date

Column D

Child's Age at time of Health Appraisal (formula needs to calculate age showing years and months)

Column E

Health Appraisal Expire Date (for children birth-2 1/2 years old their health appriasal expires after 13 months and for children 2/12-6 their health appraisal expires every 2 years)

Column F

Status (expired or current)

If possible- would there be a way to highlight expired appraisals in Red, Current appraisals in Green and highlight appraisals that will be expiring in 30 days in yellow.

I am a childcare provider and this sheet will help me keep track of their health appraisals for Licensing compliance. I am familiar with excel, but haven't ever used excel formulas for dates.

Thanks in advance for the help!!!!

Answer 1

Hi,

46313 is Excel’s internal date serial number. The formula is likely working; the cell just needs to be formatted as a date.

Select column E, then go to Home > Number Format > Short Date.

For row 2, you can use:

• D2 – Age at appraisal =IF(OR(B2="",C2=""),"",DATEDIF(B2,C2,"Y")&" years, "&DATEDIF(B2,C2,"YM")&" months")
• E2 – Appraisal expiration date =IF(C2="","",EDATE(C2,IF(DATEDIF(B2,C2,"M")<30,13,24)))

This gives children under 30 months a 13-month expiration and children 30 months or older a 24-month expiration.

• F2 – Status =IF(E2="","",IF(E2<TODAY(),"Expired",IF(E2<=TODAY()+30,"Expiring in 30 days","Current")))

You can then use Conditional Formatting on column F:

• "Expired" = red
• "Current" = green
• "Expiring in 30 days" = yellow

Then copy the formulas down for the other students.

Just to confirm, is the cutoff exactly 2 years 6 months (30 months) at the time of the health appraisal?

Click "Add comment" to let me know.

Thanks!

Answer 2

Use these formulas starting in row 2:

• D2: age at the time of the health appraisal

    =DATEDIF(B2,C2,"Y")&" years, "&DATEDIF(B2,C2,"YM")&" months"
  

• E2: appraisal expiration date

    =IF(C2="","",IF(C2<DATE(YEAR(B2)+2,MONTH(B2)+6,DAY(B2)),DATE(YEAR(C2),MONTH(C2)+13,DAY(C2)),DATE(YEAR(C2)+2,MONTH(C2),DAY(C2))))
  

  This works as follows:
  • if the child is still under 2 years 6 months old on the appraisal date, the expiration is 13 months after the appraisal date
  • otherwise, the expiration is 2 years after the appraisal date
• F2: status

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

Then fill the formulas down the column.

For the date logic:

• DATE(YEAR(...),MONTH(...),DAY(...)) can add years and months to a date.
• DATEDIF calculates the difference between two dates.
• TODAY() returns the current date.

For color highlighting with Conditional Formatting, select the range to format, then create these rules using Use a formula to determine which cells to format:

• Red for expired

    =$E2<TODAY()
  

• Yellow for expiring in 30 days

    =AND($E2>=TODAY(),$E2<=TODAY()+30)
  

• Green for current

    =$E2>TODAY()+30
  

If ##### appears in a date cell, widen the column.

---

• Examples of common formulas in lists
• Format a date the way you want in Excel
• Insert the current date and time in a cell
• Conditional Formatting Help - Microsoft Q&A
• Examples of common formulas in lists