Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

This Month and Next Three Months in a date

Status
Not open for further replies.

sap1958

Technical User
Oct 22, 2009
138
US
I have a Hire Date field as follows:
HireDate (datetime)
I want an indicator if the hire date is within this month or currentdate, next month or month after next.

If the month of currentdate falls within this month I want to assign it a '1'

if it is next month I want to assign it a 2

For the month after, I want to assign a 3.

I then want to filter and choose just those date records that fall with the current month through the next three months, using a calendar month. I do not think there is a function that would allow me to combine three months into one statement
 
I believe this will work for you, if I am understanding your question correctly.

Make a new formula field. I'll use the name DateChk for this example. Place the following formula in this field.

IF year(hiredate) = year(currentdate()) AND month(hiredate) = month(currentdate()) THEN 1
ELSE IF year(hiredate) = year( DateAdd ("m",1, currentdate())) AND month(hiredate) = month( DateAdd ("m",1, currentdate())) THEN 2
ELSE IF year(hiredate) = year( DateAdd ("m",2, currentdate())) AND month(hiredate) = month( DateAdd ("m",3, currentdate())) THEN 3

You could add as many months as you wanted using this setup.
Then in your filter field simply put that {DateChk} in 1 to 3.

Hope that helps.
 
You could just use a record selection formula like this:

{table.date} in date(year(currentdate),month(currentdate),1) to dateserial(year(currentdate),month(currentdate)+3,1)-1

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top