# Help with date format

I need to know how to return the datediff between two values in a field formula I tried the syntax below (see code) with no luck:

``````If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
Date({Months_Of_Service.DATE_START}) - Date(year(CurrentDate))
else
0
``````
LVL 18
###### Who is Participating?

Commented:
You can get a pretty accurate conversion from days to years by dividing by 365.25.  That's the simplest approach.  For example:

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
totext(DateDiff ("d", {Months_Of_Service.DATE_START}, CurrentDate) / 365.25 * 10,2)

If you wanted, you could get more detailed, for example by getting the difference in whole years, and then getting a separate count for the additional days and converting that to a fraction of a year (taking into account whether or not a leap year was involved).

James
0

Commented:
Try

If {Months_Of_Service.HR_JOB_CLASS_CODE} = "CE" then
Datediff("yyyy",{Months_Of_Service.DATE_START}, CurrentDate )
else
0

What are you trying to find months, years , weeks?
0

Software Solutions ConsultantCommented:

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

0

Commented:
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
0

Commented:
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
0

Security ProfessionalAuthor Commented:
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
0

Security ProfessionalAuthor Commented:
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-
0

Security ProfessionalAuthor Commented:
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-
0

Commented:
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
0

Security ProfessionalAuthor Commented:
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-
0

Commented:
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
0

Commented:
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
0

Commented:
> 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
0

Security ProfessionalAuthor Commented:
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-
0

Security ProfessionalAuthor Commented:
CE or 10, example 3 years of service should display as 30 months of service.
0

Security ProfessionalAuthor Commented:
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-
0

Commented:
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
0

Security ProfessionalAuthor Commented:
Well I would like it to be more exact to the day it is run.  Is that possible?
0

Commented:
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
0

Security ProfessionalAuthor Commented:
Two decimal places seems like it would be ok, yes.

-D-
0

Security ProfessionalAuthor Commented:
0

Commented:
You're welcome.  Glad I could help.

James
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.