DateDiff partly failing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robbin - 23 Jan 2008 16:20 GMT

I have an expression that seems to work fine, unless the [EndDateofRank] is
populated. Iif the [EndDateofRank] is populated, it subtracts it from the
[AcademicYearEnding]. I can see that this is exactly what the expression
says, but not what I need. I need the difference between the BegDateofRank
and the [EndDateofRank] if the [EndDateofRank] is populated.

YearsInRank:
IIf(Month(Date())<7,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank]))-1,TYears.AcademicYearEnding-DatePart("yyyy",nz([EndDateofRank],[BegDateofRank])))

Seems like the [AcademicYearEnding] and the [EndDateofRank] should be
switched, but I can't make it work.

Can anyone help?

Thank you!

--
Robbin

Reply to this Message

John Spencer - 23 Jan 2008 17:01 GMT

PERHAPS:

YearsInRank:
IIF(EndDateOfRank is Null, AcademicYearEnding - Year(BegDateOfRank),
Year(EndDateOfRank)-Year(BegDateOfRank)) - IIF(Month(Date())<7,1,0)

OR
IIF(EndDateOfRank is Null, AcademicYearEnding, Year(EndDateOfRank) ) -
Year(BegDateOfRank) - IIF(Month(Date())<7,1,0)

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have an expression that seems to work fine, unless the [EndDateofRank] is
> populated. Iif the [EndDateofRank] is populated, it subtracts it from the
[quoted text clipped - 12 lines]
>
> Thank you!

Reply to this Message

Robbin - 23 Jan 2008 17:16 GMT

John,
You must be getting close, but the (If 7/1) part is to accomodate the fiscal
year. I'm asking it not to add another year until 7/1. Does that make sense?

Signature

Robbin

> PERHAPS:
>
[quoted text clipped - 21 lines]
> >
> > Thank you!

Reply to this Message

John Spencer - 23 Jan 2008 17:33 GMT

Since I am not sure of the logic you want, all I can suggest is that you
adjust the IIF (Month(Date())<1 part of the expression. Right now it will
subtract 1 if the current date is before July 1. Do you want it to do
something else? If so, what?

Otherwise, describe in words what you want the calculation to be. Something
like:
I want to calculate year in rank based on the difference in years between
Begin date and end date.
If there is no end date I want to use the Academic year in place of end
date.
If I use Academic year I want to subtract 1 year if the current date is
before July 1.
If I use begin date and end date I do not want to subtract 1
IF begin date and end date both have values I want the calculation to be in
whole years only. So the day and month of EndDate must be after the day and
month of begin date.

You might be better off using
Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)) to calculate the date
to be used

And this expression to calculate the number of whole years.
DateDiff("yyyy",BegDateOfRank,Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)))
+
Format(BegDateOfRank,"mmdd") >
Format(Nz(EndDateOfRank,DateSerial(AcademicYearEnding,6,30)),"mmdd")

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> John,
> You must be getting close, but the (If 7/1) part is to accomodate the
[quoted text clipped - 30 lines]
>> >
>> > Thank you!

Reply to this Message

Robbin - 23 Jan 2008 17:47 GMT

Sorry about the ambiguity. I would like the expression to give me the years
in rank subtracting the end date from the start date, or use the clock where
there is no end date. BUT- I don't want it to add the next year on the
change of the calendar year, rather on the change of the fiscal year, which
is 7/1. So, if it's between January and July, it has to subract 1. After
July 1, it can simply calcualate the actual difference.

And the funny thing is, all of that part is working correctly. It just
won't subract the enddate from the start date. I ignores the enddate and
continues to use the clock date when an endate is entered.
I hope this helps.

Signature

Robbin

> Since I am not sure of the logic you want, all I can suggest is that you
> adjust the IIF (Month(Date())<1 part of the expression. Right now it will
[quoted text clipped - 58 lines]
> >> >
> >> > Thank you!

Reply to this Message

Robbin - 18 Feb 2008 15:53 GMT

Hi Folks,

I'm still struggling with this formula -- it's so close, but something is
still wrong: YearsInRank:
IIf(Month(Date())>7,DateDiff("yyyy",[BegDateofRank],nz([EndDateofRank],[FiscalYear]),DateDiff("yyyy",[BegDateofRank],nz([EndDateofRank],[FiscalYear])-1)

I'd like this formula to calculate the years in rank based on the Beginning
date of Rank, minus the end date of rank, using the [FiscalYear] field as the
end date of rank where that field is blank. I created a field called
[FiscalYear], which returns a value of 07/01"YYYY" for whichever year is in
the AcademicYearEnding field.

The formula seems to be working fine, except that where the begining date is
07/01/2006 and the end date is 07/01/2007, I get a 0 in the Years in Rank
field UNLESS I roll the computer clock past July 1 in 2008. Only then does
it give me a 1 in the Year in rank field. Since both begining and ending
dates preceed the FiscalYear of 07/01/2008, it should simply return a value
of 1, right?

I've tried about 10 different suggestions and iterations, to no avail.
Thank you for any help you can offer.

Thank you.

Signature

Robbin

> Sorry about the ambiguity. I would like the expression to give me the years
> in rank subtracting the end date from the start date, or use the clock where
[quoted text clipped - 70 lines]
> > >> >
> > >> > Thank you!

Reply to this Message