Date Formula 1

[blairclw]

I have a report that shows me the employees HIREDATE. What I would like is to be able to only pull employees who's anniversary date is for the current month.....
{EmployeePos_Stat_Curr.LastHireDate}

 

[kskid]

Try this

Month({EmployeePos_Stat_Curr.LastHireDate}) = month(currentdate))

-lw

 

[blairclw]

Thanks.....maybe you can tell me this. I have years of service using this formula that works great.
Datediff ("yyyy", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)


The thing is, if the person has been here less then 1 year it will show 0. Is there a way, I can have it show their actual months. So if that person has only been here for 8 months, that is what it will show vs. 0 ?

 

[kskid]

I just happened to have what you are looking for.

if isnull({EmployeePos_Stat_Curr.LastHireDate}) then 0
else If cstr({EmployeePos_Stat_Curr.LastHireDate},"MMdd") <= cstr(CurrentDate,"MMdd") then 
Year(CurrentDate) - Year({EmployeePos_Stat_Curr.LastHireDate}) 
else 
Year(CurrentDate) - Year({EmployeePos_Stat_Curr.LastHireDate}) - 1

-lw

 

[kskid]

Ooops... misread the post. For our purposes, reporting YOS is based on completed years only.

 

[blairclw]

Still showing 0 for those less then 1 year. Any other suggestions?

 

[kskid]

OK. Try this....

numbervar n;

if isnull({EmployeePos_Stat_Curr.LastHireDate}) then 0
else If cstr({EmployeePos_Stat_Curr.LastHireDate},"MMdd") <= cstr(CurrentDate,"MMdd") then 
n := Year(CurrentDate) - Year({EmployeePos_Stat_Curr.LastHireDate}) 
else 
n := Year(CurrentDate) - Year({EmployeePos_Stat_Curr.LastHireDate}) - 1;

if n > 0 then totext(n,0) & " Yrs"
else  totext(datediff("m",{EmployeePos_Stat_Curr.LastHireDate},currentdate),0) & " Mo"

 

[blairclw]

Nope,it still shows 0 with the word months behind it.

 

[synapsevampire]

First, this is probably incorrect:

Datediff ("yyyy", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)

All that does is subtract the year portions, so if they started work on December 31st, and you ran it on January 1st, they'd have worked 1 year, however if they started on January 1st and you ran it on December 31st, it would be 0 years.

Anyway, you can just base it on the value of your formula:

if Datediff ("yyyy", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate) < 1 then
"Months "& Datediff ("m", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)
else
"Years "& Datediff ("m", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)

-k

 

[blairclw]

This one might be easier.....based on your formula to pull in current month
Month({EmployeePos_Stat_Curr.LastHireDate}) = month(currentdate))

Instead of pulling in "current" month, how can I pull in whatever month I need? I tried changing the last part of the formula but its not working.

 

[kskid]

That's strange.... it works for me. I am using Crystal 8.5 with ODBC Connection to Oracle DB

I simply replaced my field name with yours.

I basically added a local variable, n and evaluated it.

After assigning the years to n, I then check to see if n = 0. If not = 0, then I convert the number to a text format and add "Yrs" after it. Otherwise, I use the datediff to get the months and add "mo" after it to indicate months

You might check out the hire_date. If less than a month, you will get a 0 with "Mo" added on.

-lw

 

[blairclw]

I'm sorry, guys I don't know why I can't get this to work. I'm copying your formula over into my report and there are no errors but when I run it, it doesn't give me 8 months it stills give me 0 months.

Thanks for all the help. I've learned Crystal on my own and I'm not up on all the technical formulas. I'm still in the learning curve on this one.

 

[kskid]

Post the formula and the records that you are evaluating.

You might change the record selection to isolate it to just this year's data and reduce the volume.

{EmployeePos_Stat_Curr.LastHireDate} in YearToDate

 

[blairclw]

In this formula, this will pull in the current employees who's hire date was in the current month. How can I do the formula where if I need to pull September employees now, I can?


Month({EmployeePos_Stat_Curr.LastHireDate}) = month(currentdate)

 

[kskid]

If you are not wanting to use parameters, then

month({EmployeePos_Stat_Curr.LastHireDate})= month( dateserial(year(currentdate),month(currentdate)+1,1))

-lw

 

[synapsevampire]

My formula had a typo:

if Datediff ("yyyy", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate) < 1 then
"Months "& Datediff ("m", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)
else
"Years "& Datediff ("yyyy", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)

-k

 

[blairclw]

Synapsevampire, the formula is still not pulling in just months. It now is rounding the 0 up to 1. So if the employee has been here 8 months, it shows year 1.

 

[Turkbear]

Hi,
Please copy your formula exactly as written and indicate where it is placed in the report..Also provide some of the dates that cause the 0 result..

I just tested Synapse's code on my data ( very similar to yours) and it worked fine..

BTW, what database and connectivity?








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[blairclw]

if isnull({EmployeePos_Stat_Curr.LastHireDate}) then 0
else If cstr({EmployeePos_Stat_Curr.LastHireDate},"MMdd") <= cstr(CurrentDate,"MMdd") then
Year(CurrentDate) - Year({EmployeePos_Stat_Curr.LastHireDate})
else
Year(CurrentDate) - Year({EmployeePos_Stat_Curr.LastHireDate}) - 1

I actually created a formula for Years of Service by using their Last hire date and then placed it in my report. I'm using Crystal Reports 9.

Some dates are:

Hire date
9/14/2004
9/23/2004
9/28/2004

 

[Turkbear]

Hi,
The formula you posted is not the same as the one Synapse posted did you try?

if isnull({EmployeePos_Stat_Curr.LastHireDate}) then 0
else if Datediff ("yyyy",({EmployeePos_Stat_Curr.LastHireDate}}, CurrentDate) < 1 then
"Months "& Datediff ("m", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)
else
"Years "& Datediff ("yyyy", {EmployeePos_Stat_Curr.LastHireDate}, CurrentDate)

To Paraphrase:"The Help you get is proportional to the Help you give.."