How can I write the formula keep to the current year 2018 same until 10/1/2019 ? When the date hits 10/1/2019, the year should become 2019. Similarly when the date hits 10/1/2020, the year becomes 2019.
Example below:
HTML Code:
How can I write the formula keep to the current year 2018 same until 10/1/2019 ? When the date hits 10/1/2019, the year should become 2019. Similarly when the date hits 10/1/2020, the year becomes 2019.
Example below:
HTML Code:
Did you try this:
=YEAR(A1)
Or maybe this:
=IF(MONTH(A1)>9,YEAR(A1)+1,YEAR(A1))
Last edited by AliGW; 10-25-2018 at 04:18 PM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
The year becomes 2019 for the date 10/1/2018 when I use your formula. I need to keep the year as 2018 until 9/30/2019. On 10/1/2019, the year becomes 2019.
It shouldn’t, since the formula is looking for a month LARGER than 9. Did you actually try it? I can’t at present as I am away from my PC.
I did try it. The year is showing up as 2019 using your formula when I use the date 10/1/2018 as A1 cell value.
It shouldn’t. Can you attach a file?
Hi,
Above in Red, assuming you meant 2020:
Excel 2016 (Windows) 64 bit
A B C 1Date Year 2 9/1/2018 2017 3 10/1/2018 2018 4 11/1/2018 2018 5 12/1/2018 2018 6 1/1/2019 2018 7 2/1/2019 2018 8 3/1/2019 2018 9 4/1/2019 2018 10 5/1/2019 2018 11 6/1/2019 2018 12 7/1/2019 2018 13 8/1/2019 2018 14 9/1/2019 2018 15 10/1/2019 2019 16 11/1/2019 2019 17 12/1/2019 2019 18 1/1/2020 2019 19 2/1/2020 2019 20 3/1/2020 2019 21 4/1/2020 2019 22 5/1/2020 2019 23 6/1/2020 2019 24 7/1/2020 2019 25 8/1/2020 2019 26 9/1/2020 2019 27 10/1/2020 2020
Sheet: Sheet121
Excel 2016 (Windows) 64 bit
C 1Year 2 =IF(OR(MONTH(A2)={10,11,12}),YEAR(A2),YEAR(A2)-1)
Sheet: Sheet121
Formula copied down.
Sorry - I got it wrong. Give me a minute.
Never mind - you have a solution now.
Alternatively:
=IF(MONTH(A1)>9,YEAR(A1),YEAR(A1)-1)
Thanks jtakw! The condition is actually like this
If year = '2018' and month(date) in (10,11,12) then year = '2018'
If year = '2019' and month(date) in (01,01,03,04,05,06,07,08,09) then year = '2019'
I believe that is what Jtakw’s and my last formula both do.
Yes ... and? It’s not supposed to show 2019 until October, is it?
I apologize if I'm not clarifying the requirement correctly. Here is the actual requirement.
The year should be 2019 for January through September 2019 when the current year is 2018 and months in (10,11,12)
The year should be 2020 for January through September 2020 when the current year is 2019 and months in (10,11,12)
No, sorry - you’ve lost me.
So does my solution in post #10. I think Donny needs to define what exactly he means by ‘current year’.
Apologize again. Let me explain it clearly.
My formula shows MM-YY as 10-18 using this formula =TEXT(DATE(YEAR(TODAY()),10,1),"MM-YY")
The problem is I want the 10-18 header to remain same until next year 10/01/2019 but using the formula above I'm afraid that Year(Today()) will change to 2019 by 01/2019. I want 10-18 to be 10-18 until 10-19. When the date hits 10/01/19, the MM-YY header should be automatically rollover to 10-19.
How about this?
=IF(MONTH(TODAY())>9,TEXT(DATE(YEAR(TODAY())-1,10,1),"MM-YY"),TEXT(DATE(YEAR(TODAY()),10,1),"MM-YY"))
Your last description in Post # 19 is completely different than your OP.
Replace your current formula to this:
Formula:Please Login or Register to view this content.
Oh, I give up! Sorry, it’s getting late and I’m not following the twists and turns here. I hope you get it sorted. Goodnight, all!
Goodnight AliGW zzzzzzzzzz...
I am just wondering, did you try my formula in Post # 21 ??
LOL, No you don't, just change the TODAY() reference in the formula to a Cell reference to Test, like this (You can put ANY date you want in Column A for testing):
Excel 2016 (Windows) 64 bit
A B I J 1Date 10-18 2 9/1/201810-17 3 10/1/201810-18 4 11/1/201810-18 5 12/1/201810-18 6 1/1/201910-18 7 2/1/201910-18 8 3/1/201910-18 9 4/1/201910-18 10 5/1/201910-18 11 6/1/201910-18 12 7/1/201910-18 13 8/1/201910-18 14 9/1/201910-18 15 10/1/201910-19 16 11/1/201910-19 17 12/1/201910-19 18 1/1/202010-19 19 2/1/202010-19 20 3/1/202010-19 21 4/1/202010-19 22 5/1/202010-19 23 6/1/202010-19 24 7/1/202010-19 25 8/1/202010-19 26 9/1/202010-19 27 10/1/202010-20
Sheet: Sheet121
Excel 2016 (Windows) 64 bit
I J 1=TEXT(DATE(YEAR(TODAY())-IF(MONTH(TODAY())>9,0,1),10,1),"MM-YY") 2=TEXT(DATE(YEAR(A2)-IF(MONTH(A2)>9,0,1),10,1),"MM-YY")
Sheet: Sheet121
Copy J2 formula down Column to test...
Awesome! Thank you very much. I will mark this thread as resolved now.
Morning, all! Glad to see you got there in the end.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks