Date() at fiscal year

R

Robbin

Hi,

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)+[CreditServiceYears])


Can anyone help? Thank you!
 
R

Robbin

Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin


Jeff Boyce said:
Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Robbin said:
Hi,

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!
 
R

Robbin

I have put this together out of my limited understanding:

YearsExperience:
IIf(DateSerial(Year(Date())<7,1,DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])),DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date()-1)+[CreditServiceYears]))

Seems to be working, and I tested it by changing my computer date to July 8,
and it still seems to work. But the question is, should it? Structuring it
the opposite way, (> vs. <) or putting the -1 in the opposite argument,
doesn't work. I fear my argument is sloppy.

Thank you,
--
Robbin


Robbin said:
Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin


Jeff Boyce said:
Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Robbin said:
Hi,

I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.

YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!
 
J

Jeff Boyce

Robbin

My suggestion would be to throw all your potential "boundary conditions" at
it to see.

Right off the top, you'd need to know that it worked on dates immediately
before and immediately after your transition (?7/1/yyyy). You'd need to
know that it worked from one year to the next (2007, 2008, 2009). And you'd
need to know that it worked (i.e., did NOT trigger) for dates around the new
year (12/31 vs 1/1).

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Robbin said:
I have put this together out of my limited understanding:

YearsExperience:
IIf(DateSerial(Year(Date())<7,1,DateDiff("yyyy",[DateofHire],nz([DateofTTHir
e],Date())+[CreditServiceYears])),DateDiff("yyyy",[DateofHire],nz([DateofTTH
ire],Date()-1)+[CreditServiceYears]))

Seems to be working, and I tested it by changing my computer date to July 8,
and it still seems to work. But the question is, should it? Structuring it
the opposite way, (> vs. <) or putting the -1 in the opposite argument,
doesn't work. I fear my argument is sloppy.

Thank you,
--
Robbin


Robbin said:
Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin


Jeff Boyce said:
Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I have an expression that works perfectly, (I got it here, I think),
except
for one minor problem. I need the date to increase by one year, this
year,
as of July 1 of this year. My professors don't get another year's
experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get
the
wrong answer.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!
 
R

Robbin

Hi Jeff,

Thank you for your pointers. I will put it through your tests and see if it
holds up. One thing I discovered was that putting the -1 after Date() caused
it to ignore the +[CreditServiceYears], so I had to move the -1 after
+[CreditServiceYears]. Seems to be working better now for that part.

If you get a chance, would you please take a hard look at it for logic?
Darned if it doesn't seem to be working backwards!
Thanks again.
--
Robbin


Jeff Boyce said:
Robbin

My suggestion would be to throw all your potential "boundary conditions" at
it to see.

Right off the top, you'd need to know that it worked on dates immediately
before and immediately after your transition (?7/1/yyyy). You'd need to
know that it worked from one year to the next (2007, 2008, 2009). And you'd
need to know that it worked (i.e., did NOT trigger) for dates around the new
year (12/31 vs 1/1).

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Robbin said:
I have put this together out of my limited understanding:

YearsExperience:
IIf(DateSerial(Year(Date())<7,1,DateDiff("yyyy",[DateofHire],nz([DateofTTHir
e],Date())+[CreditServiceYears])),DateDiff("yyyy",[DateofHire],nz([DateofTTH
ire],Date()-1)+[CreditServiceYears]))

Seems to be working, and I tested it by changing my computer date to July 8,
and it still seems to work. But the question is, should it? Structuring it
the opposite way, (> vs. <) or putting the -1 in the opposite argument,
doesn't work. I fear my argument is sloppy.

Thank you,
--
Robbin


Robbin said:
Dear Jeff,

I do believe you are right. Can you help me with what that would look like?
--
Robbin


:

Do you need to use an IF() or IIF() statement to evaluate how to handle a
date before 7/1 and after 7/1?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi,

I have an expression that works perfectly, (I got it here, I think),
except
for one minor problem. I need the date to increase by one year, this
year,
as of July 1 of this year. My professors don't get another year's
experience
until they finish the second semester, on the fiscal year, July 1.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])

This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get
the
wrong answer.

YearsExperience:

DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)
+[CreditServiceYears])


Can anyone help? Thank you!
 
J

John Smith

In case it may help, my route to mapping Financial onto Calendar years is just
to subtract the appropriate number of months to bring them back to Jan 1. For
your Financial year starting on July 1 that would be six. Assuming that your
CreditServiceYears is a number of years then it will need to go outside of the
DateDiff():

YearsExperience:
DateDiff("yyyy", DateofHire, DateAdd("m", -6, Nz(DateofTTHire, Date()))) +
CreditServiceYears

HTH
John
##################################
Don't Print - Save trees
 
R

Robbin

John,

That seems to stand up to the tests, and is certainly a cleaner solution.
Thank you!
--
Robbin


John Smith said:
In case it may help, my route to mapping Financial onto Calendar years is just
to subtract the appropriate number of months to bring them back to Jan 1. For
your Financial year starting on July 1 that would be six. Assuming that your
CreditServiceYears is a number of years then it will need to go outside of the
DateDiff():

YearsExperience:
DateDiff("yyyy", DateofHire, DateAdd("m", -6, Nz(DateofTTHire, Date()))) +
CreditServiceYears

HTH
John
##################################
Don't Print - Save trees
I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])
This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)+[CreditServiceYears])
 
R

Robbin

Hi John,

I just noticed that this is not affecting the nz part: [DateofTTHire]. I
tried this but it didn't work:

YearsExperience:
DateDiff("yyyy", [DateofHire], DateAdd("m", -6, Nz([DateofTTHire], DateAdd("m", -6, Date()))) +
[CreditServiceYears]
--
Robbin


John Smith said:
In case it may help, my route to mapping Financial onto Calendar years is just
to subtract the appropriate number of months to bring them back to Jan 1. For
your Financial year starting on July 1 that would be six. Assuming that your
CreditServiceYears is a number of years then it will need to go outside of the
DateDiff():

YearsExperience:
DateDiff("yyyy", DateofHire, DateAdd("m", -6, Nz(DateofTTHire, Date()))) +
CreditServiceYears

HTH
John
##################################
Don't Print - Save trees
I have an expression that works perfectly, (I got it here, I think), except
for one minor problem. I need the date to increase by one year, this year,
as of July 1 of this year. My professors don't get another year's experience
until they finish the second semester, on the fiscal year, July 1.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],Date())+[CreditServiceYears])
This calculates the numberf of years experience based on the current date,
however, I need the Date() to be as of 7/1 of the current year. I tried
serializing the date() part to DateSerial(Year(Date()),7,1), but it didn't
work. I must have something mixed up. I don't get an error, I just get the
wrong answer.
YearsExperience:
DateDiff("yyyy",[DateofHire],nz([DateofTTHire],(DateSerial(Year(Date()),7,1)+[CreditServiceYears])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fiscal year 5
End of Quarter Based on Fiscal Year 2
Update on fiscal year 0
Financial Year Reporting 4
DateDiff issue 7
Date Time decimal value help please 1
Default date 3
Date Differences in Years 3

Top