Help with date format

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
DateDiff("d",{Months_Of_Service.DATE_START},CurrentDate)

Your formula should hae worked except you took the year for the current date

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
Date({Months_Of_Service.DATE_START}) - Date(CurrentDate)
else
0

mlmcc

Actually, mlmcc, Date (CurrentDate) will give you an error (at least, in CR 10).  Date requires a datetime argument.  CurrentDate by itself should work.

 James

The formula result must be a string is the error that is returned when using this:

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
Date({Months_Of_Service.DATE_START}) - (CurrentDate)
else
0

Ok I am kind of getting somewhere now... The following:

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
totext(Date({Months_Of_Service.DATE_START}) - Date(year(CurrentDate)))
else
"Nothing"

Is working but what I am looking to do is calculate the months of service given up and until the current date.  The formula above is not rendering that.

-D-

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
totext(Date(year(CurrentDate)) - Date(Year({Months_Of_Service.DATE_START})* 10 ))
else
"Nothing"

The above is what I am looking to accomplish.  With the formula above though the * 10 does not seem to be applied... Thoughts?

-D-

Where/How are you using that formula?  The formula that you posted and, consequently, the formulas suggested by others, all return a number.  If you're getting an error saying that the result must be a string, it's because of where/how you're using that formula.

 Or, to put it another way, what output do you _want_ the formula to produce?

 You say that you want months, but if the formula is supposed to produce a string, what do you want it to say?

 Do you want whole months or something more detailed?  For example, would 01/10 - 02/20 (41 days) be 1 month?

 If you're looking for whole months, try:

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
  DateDiff ("m", {Months_Of_Service.DATE_START}, CurrentDate)


 Note that this is essentially the same thing suggested by the first two replies to your question.  It's just a matter of changing the first argument to DateDiff to get the correct "units" (days, weeks, months, etc.).

 To get that number as a string:

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
  CStr (DateDiff ("m", {Months_Of_Service.DATE_START}, CurrentDate), "#")


 James

James,
    Thank you for your replies so far.  The following:

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
totext(DateDiff ("m", {Months_Of_Service.DATE_START}, CurrentDate),0)
else
"Nothing"

With your changes renders the months of service.  The problem I have is Job Class "CE" is considered a 10 month position and each year of service should render only 10 months of credit.  How would you work that into the formula?

-D-

You had not posted that last message when I started writing my last message.

 What are you trying to do in that last formula, because what you are actually doing makes no sense at all.

 "Date(year(CurrentDate))"

 Year (CurrentDate) is 2009.
 Date (2009) converts that number to a date, which gives you 07/01/1905 (July 1st, 1905).


 "Date(Year({Months_Of_Service.DATE_START})* 10 )"

 Year({Months_Of_Service.DATE_START}) will give you the year from that date.  Let's say it was 2008.

 Date (2008 * 10) = Date (20080), which gives you 12/22/1954.


 07/01/1905 - 12/22/1954 is -18,071 days, which I'm pretty sure is not what you're looking for.  :-)

 James

OK, we're cross-posting here (I was afraid that might happen).  Give me a little time to look at your last post and I'll post a reply.

 James

> The problem I have is Job Class "CE" is considered a 10 month position
 > and each year of service should render only 10 months of credit.

 I'm not sure what you mean there.  Are you saying that 1 year (12 months) only counts as 10 months somehow, or 10 months count as 1 year, or ...?

 This basically brings me back to what I asked before.  What output do you want the formula to produce?

 How about some specific examples?  Using 9, 10, 11, 12 and 13 months as examples, what should the formula say for each of those?

 James

CE = Each year of service should only count at 10 months
14 = Each year of service should be counted as 14 months


14, example 3 years of service should display as 42 months.  Is this making sense?

-D-

CE or 10, example 3 years of service should display as 30 months of service.

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
totext(DateDiff ("yyyy", {Months_Of_Service.DATE_START}, CurrentDate)*10,0)


This gives me 30 which I would expect.  

-D-

So, you don't really want to count the months between the dates.  You want to count the years and then convert that to a corresponding number of months (years * 10 or years * 14).  Correct?

 Do you care about parts of a year?  For example, if the dates are 20 months apart, that's 1.67 years.  Does that just count as 1 year?

 If so, then what you've got there should work.  Do the same kind of thing for code "14", but multiply by 14.

 James

Well I would like it to be more exact to the day it is run.  Is that possible?

I'm not sure what you mean.  Are you saying that if, for example, the two dates were 400 days apart, which translates to approx 1.095 years, you'd want to multiply that by 10 or 14, giving you 10.95 or 15.33 months, respectively?  If so, do you want to show any decimal places on the resulting month count?

 James

Two decimal places seems like it would be ok, yes.

-D-

Thanks for your assistance!