Comparing dates in Excel-CodePudding

I'm trying to compare date in excel to return a value.

I want to see...

if date of birth   today() is less than DATE(2021,9,1) years then output 6
if date of birth   today() is less than DATE(2021,9,1) years then output 5
if date of birth   today() is less than DATE(2021,9,1) years then output 4
if date of birth   today() is less than DATE(2021,9,1) years then output 3

This is for a school project. I'm struggling to understand the date concept.

Can someone point me in the right direction?

Thanx for reading,

Sam

CodePudding user response：

This should work, imagining that cell A1 contains the date of birth:

=IF((A1   today()) < DATE(2021,9,1), 6)
=IF((A1   today()) < DATE(2021,9,1), 5)
=IF((A1   today()) < DATE(2021,9,1), 4)
=IF((A1   today()) < DATE(2021,9,1), 3)

CodePudding user response：

After going home and sleeping on it, I think I figured it out. I wanted my date to be static and check against pupil date of births to determine their age for a particular year. I already have a version of this which is dynamic and updates itself when the sheet is loaded. Here is what I did, where E3 is equal to a pupils date of birth...

=IF(E3="","",
IF(DATEDIF(E3,DATE(2021,9,1),"Y") 1>=11,6,
IF(DATEDIF(E3,DATE(2021,9,1),"Y") 1>=10,5,
IF(DATEDIF(E3,DATE(2021,9,1),"Y") 1>=9,4,
IF(DATEDIF(E3,DATE(2021,9,1),"Y") 1>=8,3,
"")))))

If there is an easier way to accomplish this I would relish feedback.

Thanx again to those of you who replied,

Sam

This is my dynamic version

=IF(E3="","",MAX(0,DATEDIF(DATE(YEAR(E3 122),9,1),NOW(),"Y" )-4))

This calculates age

=IF(E3="","",
DATEDIF(E3,TODAY(),"Y") & "." &
DATEDIF(E3,TODAY(),"YM") & "." &
DATEDIF(E3,TODAY(),"MD")
)

CodePudding user response：

In Excel, a date is stored as a number of days since 1/1/1900 (or 1904, not very important), you can subtract date2-date1 and that will return the number of days between the 2 dates.
With that logic, a simple formula to get your age from your birth date stored in E3 would be:

=INT((TODAY()-E3)/365)

Just for fun, enter a date in a cell, then Clear Formats. You will see the number of days.