Hello Everyone this is my first post asking for Excel help.

Please be patient with me.

Currently I am creating a spreadsheet tracking hours.

Maybe I am using the wrong formula?

=IF(AND([@[Date]]>=DATEVALUE("05/01/2021"),[@[Date]]<=DATEVALUE("05/31/2021"),[@[Present OR Absent]]="Absent"),"May Absent","May Present")

What I want to do for this Column is to show if for the Month of May if someone is physically at that location.

The Date column ranges from January 2021 - October 2021. My formula catches the May month both May Present and May Absent correctly....... BUT

For Row items outside of May, unwanted results populated for months outside of May ie in 06/01/2021 Row I will see "May Present"

Is there another formula better fitting for what I am looking for? If so what is it?

Secondly how can I eliminate "May Present" for months outside of May? I just want a blank cell maybe ""?

The date Column is the First Column (A). Present or Absent Column (H) is to the Right of the Date Column and the Month Absent/Present Indicator Column (M) is to the right of the

Thank you for reading this far.

CodePudding user response：

Nest the If checks

=IF(AND([@[Date]]>=DATEVALUE("05/01/2021"),[@[Date]]<=DATEVALUE("05/31/2021")), IF([@[Present OR Absent]]="Absent","May Absent","May Present"), "")