Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Fiscal Year

  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you! That should do it!

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    I'm confused!
    Using double minus and a greater than?

    cheers, Paul

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #6
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    I'm glad I wasn't the only one who was confused....

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    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.

    Comments?

    Fred

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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)
    Last edited by zeddy; 2015-06-30 at 06:48.

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

    access-mdb (2015-06-30)

  10. #9
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,725
    Thanks
    147
    Thanked 156 Times in 149 Posts
    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. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    zeddy, do you use --test rather than 1*test because --test is "faster" than the product of 1*test ??

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    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. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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. #14
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    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. #15
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    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 LastLast

Posting Permissions

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