Calculate Months since a certain date

[lwilt]

I'm trying to find a way to calculate the months since a certain date. I would like the final formula to look similar to:

Pt:IIF([Avg Order]>=601,5,IIF([Avg Order]>=351,4,IIF([Avg Order]>=201,3,IIF([Avg Order]>=100,2,IIF([Avg Order]>0,1,0)))))

but based on dates.

Date ranges would be something like last 6 months, 7 to 12 months ago, 13 to 24 months ago, 25 to 36 months ago, ect where the most recent data range would be at the beginning.

Is there a function to create the current date so I don't have to type it in every time I run the queries?

Is there a function to see how many months have passed since the date field I'm looking at and comparing to the current date?

Or would you just subtract the 2 date fields and then divide by 30 to have a rough idea of the number of months that have passed?

 

[Fowmy]

Hi,

To get the current date, use the function: TODAY()

To calculate the number of months between today and the past date, use the function: =DATEDIF(Past date,TODAY(),"M")

 

[lwilt]

I can't get the TODAY function to work....every time I type it in a blank field to get that date it doesn't work

 

[Fowmy]

It should be entered followed by =, like =today()

Please let me know the version of Excel and is it Windows or Mac ?

 

[lwilt]

I'm trying to do this in access not excel

 

[Fowmy]

Oh !,

I did not check the forum title, Hope someone could help you.

 

[alansidman]

In Access the function =Date() is equivalent to =Today() in Excel. Additionally, there is a function to calculate differences in dates. =DateDiff.

Look at this link MS Access: DateDiff Function

 

[lwilt]

appreciate it....didnt realize in access I had to do the Date function in the table itself and not the query