Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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

Open in new window

0
John Gates, CISSP
Asked:
John Gates, CISSP
1 Solution
 
BSAS_ITGuyCommented:
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
 
MIKESoftware Solutions ConsultantCommented:


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

0
 
mlmccCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
James0628Commented:
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
 
John Gates, CISSPSecurity 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
 
John Gates, CISSPSecurity 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
 
John Gates, CISSPSecurity 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
 
James0628Commented:
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
 
John Gates, CISSPSecurity 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
 
James0628Commented:
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
 
James0628Commented:
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
 
James0628Commented:
> 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
 
John Gates, CISSPSecurity 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
 
John Gates, CISSPSecurity ProfessionalAuthor Commented:
CE or 10, example 3 years of service should display as 30 months of service.
0
 
John Gates, CISSPSecurity 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
 
James0628Commented:
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
 
John Gates, CISSPSecurity ProfessionalAuthor Commented:
Well I would like it to be more exact to the day it is run.  Is that possible?
0
 
James0628Commented:
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
 
John Gates, CISSPSecurity ProfessionalAuthor Commented:
Two decimal places seems like it would be ok, yes.

-D-
0
 
James0628Commented:
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
 
John Gates, CISSPSecurity ProfessionalAuthor Commented:
Thanks for your assistance!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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