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
    3,251
    Thanks
    154
    Thanked 613 Times in 583 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,811
    Thanks
    60
    Thanked 1,090 Times in 1,013 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
    3,251
    Thanks
    154
    Thanked 613 Times in 583 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,972
    Thanks
    158
    Thanked 184 Times in 177 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,935
    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
    3,251
    Thanks
    154
    Thanked 613 Times in 583 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 05: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,972
    Thanks
    158
    Thanked 184 Times in 177 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
    3,251
    Thanks
    154
    Thanked 613 Times in 583 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,546
    Thanks
    39
    Thanked 69 Times in 65 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
    3,251
    Thanks
    154
    Thanked 613 Times in 583 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,546
    Thanks
    39
    Thanked 69 Times in 65 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,811
    Thanks
    60
    Thanked 1,090 Times in 1,013 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
    130
    Thanks
    19
    Thanked 2 Times in 2 Posts
    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
  •