PDA

View Full Version : Solved: Converting FY formula into a VBA



SeanJ
03-10-2010, 08:50 AM
I need this

=IF(MONTH(C2)>9,YEAR(C2)+1,YEAR(C2))

to be converted to something like this

txtFY = (MONTH(t.Start)>9,YEAR(t.Start)+1,YEAR(t.Start))

txtFy is a string
t.start is a part of a file record being read.

lucas
03-10-2010, 09:03 AM
Write out the logic for us please.

txtFY = the month if t.start is larger than 9 etc.

is t.Start also a string?

SeanJ
03-10-2010, 09:17 AM
This is trying to find the Financial Year between Oct and Sep.

t.start is a date field in MS Project and I can read the record, but do not know how to find in VBA the Finacical Year.

SeanJ
03-10-2010, 10:44 AM
Here is the solution that I found and made it work.

txtFY = Year(t.Start) + IIf(t.Start > DateSerial(Year(t.Start), 9, 30), 1, 0)

lucas
03-10-2010, 10:48 AM
I started to suggest dateserial but was hoping someone with more knowledge on the specific issue would come along.

Glad you hashed it out.

Bob Phillips
03-10-2010, 12:26 PM
You can make it a tad shorter



txtFY = Year(t.Start) - (t.Start > DateSerial(Year(t.Start), 9, 30))