Diff between hire date and last day of the previous month

Create 1 formula for last day of previous month
Create another formula to take the DATEDIFF between the hire date and your first formula

To get the last day of the month, subtract one day from the first day of this month
Loosely:
DATEADD(dd, -1,  (DATE(YEAR(TODAY), MONTH(TODAY), 1))


Formula 2:
DATEDIFF(dd, Formula1, HireDate)

I think dateadd("d",-datepart("d",currentDate),currentDate) should give you last day in the previous month

Can also do...

Currentdate-{table.hiredate}-day(Currentdate)+1

Of course you will get a -ve result if the hire date is in the current month.

Are you consideriing the hire date as today always?

An easy formula

Day({HireDate})

or using today

Day(CurrentDate)
The number of days from end of last month to today is always the date.

Is that what you want or do you need some other value?

mlmcc

The hire date could be any date.  No matter what the hire date is, or what today's date is, I'm trying to calculate the number of days from the hire date to the last day of the previous month.

For example:  employee was hired 3/10/2011 and I'm running the report today.  What is the difference between 3/10/2011 and April 30 (the last day of last month).  

You've given me something else to think about and that's hire dates this month (May).  I understand those will return a negative number which I will just ignore.

Thanks everyone for your help.  I'm still struggling with this one.

The interval for days is 'd' not dd as above

If you use

      ABS(DATEDIFF('d', Formula1, HireDate) )

The value will always be positive.

mlmcc

Last day of last month


DATEADD('d', -1,  (DATE(YEAR(TODAY), MONTH(TODAY), 1))

Another alternative

DateAdd('d',-Day(CurrentDate),CurrentDate)

mlmcc

Thanks!  I need the formula to either ignore the current month and result in nbr of days between hire date and the last day of the previous month - or - the formula needs to show a negative number if the hire date is after the last day of the previous month.

Try
Local DateVar LastDayLastMonth;

LastDayLastMonth := DateAdd('d',-Day(CurrentDate),CurrentDate);
DATEDIFF('d', HireDate, LastDayLastMonth )

mlmcc

Thanks!  That returns "a date is required here" and hi-lights the DateAdd('d',-Day(CurrentDate),CurrentDate);

Works great - thanks for your help!