• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

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
0
jph826
Asked:
jph826
1 Solution
 
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
 
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
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
 
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
 
jph826Author Commented:
Works great - thanks for your help!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now