1. Fiscal Year

I am trying to find a way to convert any date entered in one column into to the Fiscal Year in another column. 09/30/2012 would produce 2012 and 10/01/2012 would produce 2013. Any suggestions?

2. Hi

If your date is in cell [A1], then this formula should do the trick, eg in cell [H1]:
[H1]=YEAR(\$A1)--(MONTH(\$A1)>9)

zeddy

3. Thank you! That should do it!

4. I'm confused!
Using double minus and a greater than?

cheers, Paul

5. Hi Paul

Month(\$A1) will return a value 1 to 12
Month(\$A1)>9 returns True if month is 10, 11 or 12
double minus 'converts' True or False to 1 or 0

zeddy

6. I'm glad I wasn't the only one who was confused....

7. Hi Zeddy,

I've always been confused about double minus also, so I used F9 to evaluate this. I also used Excel's Formula Auditing | Evaluate tool.

With F9, I highlighted in the formula bar:
(Month(\$A1)>9) and got True

then clicked ESC to restore the formula and then highlighted
-(Month(\$A1)>9) and got -1

then clicked ESC to restore the formula and finally highlighted
--then clicked ESC to restore the formula and got 1

So this works as expected except it's not clear there's anything special about --. Actually it seems it was the rightmost - that converted True or False to a number.

Using Evaluate, it worked similarly. After evaluating the Year(\$A1), Excel started evaluating the 2nd term. It got to the result of TRUE for my test date of 12/1/15 (using US dating conventions). The next "evaluate" click got 2015--TRUE, which is slightly different than my F9 "manual" evaluation, and the last "evaluate" got 2016. This does seem like Excel is treating -- as something special. Certainly these last steps (Excel's Formula Auditing vs my manual F9) seem to differ.

Fred

8. Hi Fred

..sticking a minus sign in front of a negative number just turns it into a positive number.
So double minus -- is really the same as +
We are simply adding 1 to the year if the month is 10,11,or 12 as per the request.
We could've used a longer formula that perhaps would be easier to understand.
..but I like shorter formulas where possible, because I can type them quicker.

zeddy
(I'm now going to have a minus minus beer)

9. The Following User Says Thank You to zeddy For This Useful Post:

access-mdb (2015-06-30)

10. A Newcastle Pale Ale perchance!

Thanks for the explanation. I now understand how it works, but I have two problems. First is will I ever need to use it? And second, given first is true, will I remember it?

11. Hi

..I was kidding about the beer. I'm drinking tea.

You are right. It's hard to keep track of things you might or might not use later on.
But some of it sticks around in the bright recesses of the brain.
Or so I am told.

zeddy

12. zeddy, do you use --test rather than 1*test because --test is "faster" than the product of 1*test ??

13. Hi kweaver

..yes. As you probably know, all computer multiplication is, in fact, done via addition.
..but the difference would be imperceptible to us.

Sunshine, on the other hand, is very perceptible to us.
Especially where you are.
And, for those who didn't know it, sunshine 'weighs' approximately 1 kilogram.
(Per square mile that is).

zeddy

14. Zeddy, we pay a sun tax here in La Jolla for the good weather. My Eastern friends always wonder where I'm going on vacation and I remind them that I LIVE on vacation.

But, you, RG and Maud always light up this forum for everyone!

15. The Following User Says Thank You to kweaver For This Useful Post:

Maudibe (2015-07-04)

16. Should you have been able to use =YEAR(\$A1)+(MONTH(\$A1)>9) ? Works in LibreOffice and it's less typing and less confusing.

cheers, Paul

17. The Following User Says Thank You to Paul T For This Useful Post:

Jaggi (2015-07-01)

18. Hi All

@Paul your fomula works in excel as well and it gives more precise idea what is going on in the formula.

@Zeddy - Even Zeddy's formula is perfect, but it need deep understanding of the concept -- = + and ++ = +

Regards,
JD

Page 1 of 3 123 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•