Solved

# Diff between hire date and last day of the previous month

Posted on 2011-05-10
543 Views
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
Question by:jph826

LVL 18

Expert Comment

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:

Formula 2:
DATEDIFF(dd, Formula1, HireDate)
0

LVL 1

Expert Comment

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

LVL 77

Expert Comment

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

LVL 100

Expert Comment

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

Author Comment

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

LVL 100

Expert Comment

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

LVL 100

Expert Comment

Last day of last month

Another alternative

mlmcc

0

Author Comment

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

LVL 100

Expert Comment

Try
Local DateVar LastDayLastMonth;

DATEDIFF('d', HireDate, LastDayLastMonth )

mlmcc
0

Author Comment

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

LVL 100

Accepted Solution

Try it this way.  Forgot, DateAdd uses a DateTime

Local DateTimeVar LastDayLastMonth;

DATEDIFF('d', HireDate, LastDayLastMonth )

mlmcc

0

Author Closing Comment

Works great - thanks for your help!
0

## Featured Post

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
how to add IIS SMTP to handle application/Scanner relays into office 365.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…