Diff between hire date and last day of the previous month

CRV10
I need a formula to calculate the number of days between hire date and the last day of the previous month.  

For example:  If I were to run the report today (May 10), it would count the number of days between hire date and April 30th. No matter when I run the report, the count should always use the last day of whatever the previous month was.  

Any assistance would be greatly appreciated.  Thanks
jph826Asked:
Who is Participating?
 
mlmccCommented:
Try it this way.  Forgot, DateAdd uses a DateTime

Local DateTimeVar LastDayLastMonth;

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

mlmcc

0
 
UnifiedISCommented:
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)
0
 
hammam_diabCommented:

I think dateadd("d",-datepart("d",currentDate),currentDate) should give you last day in the previous month
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
peter57rCommented:
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.
0
 
mlmccCommented:
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
0
 
jph826Author Commented:
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.




0
 
mlmccCommented:
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
0
 
mlmccCommented:
Last day of last month


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

Another alternative

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

mlmcc


0
 
jph826Author Commented:
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.
0
 
mlmccCommented:
Try
Local DateVar LastDayLastMonth;

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

mlmcc
0
 
jph826Author Commented:
Thanks!  That returns "a date is required here" and hi-lights the DateAdd('d',-Day(CurrentDate),CurrentDate);
0
 
jph826Author Commented:
Works great - thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.