Excel date calculation

[Jetlag5549]

I have a rather large spreadsheet which tracks hospital patient admissions. I
need to identify only those who are admitted in the current month (say
November), but I am getting all patients admitted in November for prior
years, 2005, 2004, etc.

Is there a function that can look at the entered month/year of admission and
compare it with the current month/year to result in a TRUE condition so that
I may identify these individuals?

Thanks in advance,

 

[Gary''s Student]

Your very clear statement of the requirement makes this easy:

=IF(AND((MONTH(A1)=11),(YEAR(A1)=YEAR(NOW()))),TRUE,FALSE)
This is good for November.


=IF(AND((MONTH(A1)=MONTH(NOW())),(YEAR(A1)=YEAR(NOW()))),TRUE,FALSE)
This is good for any month

 

[Roger Govier]

Hi

Try
=TEXT(A1,"yyyy mm")=TEXT(NOW(),"yyyy mm")
This will work for all months

 

[daddylonglegs]

Another way.....

=A1-DAY(A1)=TODAY()-DAY(TODAY())

or to make that simpler set up one cell with the formula

=TODAY()-DAY(TODAY())+1

which will always generate the 1st day of the current month

then, assuming that date is in cell B1 use the formula

=A1-DAY(A1)+1=$B$1

 

[Jetlag5549]

Works great! Thanks!
Not to be too picky, but is there a way to modify the function to return 1
when the contition is true, and 0 if the condition if false?

 

[Roger Govier]

Hi

Warp the whole formula within a double unary minus.
This coerces True's to 1 and Falses's to 0
=--(TEXT(D1,"yyyy mm")=TEXT(NOW(),"yyyy mm"))

 

[Jetlag5549]

Awesome!
I have so much to learn.
Thanks for the help
Randy,

 

[Roger Govier]

You're very welcome, Randy. Thanks for the feedback.