IF AND between two dates

[#1]

Hello

I am trying to get the new starters between two set dates

I have the last day in the range: VStartDate

I have the first day in the range: VEndDate

(I know they are backwards, but the variables are used elsewhere and it makes sense, trust me)

So what I want to do is count the workers with a start date between these two dates

=count({$<[StartDate]={"<=$(VStartDate)"},[StartDate]={">=$(VEndDate)"} >} DISTINCT([Worker Full Name]))

What I believe I have is, count all start dates with a start date less than or equal to the end of the month, and greater than or equal to the beginning of the month.

However it is returning dates outside the range. Is there a problem with the formula? The only thing I can think is because I have two conditionals on the same field, but I am not 100% on how to get multi conditional in one field

Thank you for any help you can offer

[#2]

Try this

=count({$<[StartDate]={"<=$(VStartDate)>=$(VEndDate)"} >} DISTINCT([Worker Full Name]))

[sunny_talwar]

Try this:

=Count({$<[StartDate]={"$(='<=' & $(VStartDate) & '>=' & $(VEndDate))"} >} DISTINCT([Worker Full Name]))

and make sure that this expression ='<=' & $(VStartDate) & '>=' & $(VEndDate) in a text box gives you the date range you are looking for.

HTH

Best,

Sunny

[swuehl · Accepted solution]

Don't use the same field within one set modifier twice, QV will only consider one element list, I believe.

=count({$<[StartDate]={">=$(VStartDate)<=$(VEndDate)"} >} DISTINCT([Worker Full Name]))

Note that I also reversed the comparison operators, this is how I read your requirements.

[#5]

Thank you for the reply, the text box code was a huge help

[#6]

Thank you, I did have to change the operators back, as my start and end are backwards (confusing I know)

Other than that worked, thank you

[sunny_talwar]

Not a problem, I am glad I was helpful

Best,

Sunny