• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Financial Year Period Number Increment based on Month|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Financial Year Period Number Increment based on Month|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Financial Year Period Number Increm…
sp_PrintTopic sp_TopicIcon
Financial Year Period Number Increment based on Month
Avatar
Amien Attwood
Cape Town
Member
Members
Level 0
Forum Posts: 18
Member Since:
May 8, 2018
sp_UserOfflineSmall Offline
1
March 20, 2021 - 5:21 am
sp_Permalink sp_Print

Dear Mynda

I hope you can help.

Our Financial Year runs from 01 July – 30 June therefore Period 1 will be July, Period 2 will be August, Period 3 will be September, etc.

All I want is a Concatenated text string that looks as follows where the Period Number sequentially increases based on the month of the financial year:

Period # - 2020/21

NB: If you can automate the financial year in the above order as part of the concatenated text string, I would be extremely grateful.

See attached spreadsheet. At least I tried and actually got to something but to me there are too many variables i.e. formula is too long – some context below:

In Cell H4 is today’s date i.e. TODAY()

In Cell H1 is the formula to extract the Number of the Month from Today’s Date (Cell H4)

In Cell G1 is a very, very long IF Statement to extract the Period Number based on the Number of the Month in the Financial Year, and finally

In Cell C5 is how I got the Period Number to increase and do the concatenation of the text string.

I hope you or somebody can help by shortening the formula or make one formula in Cell C5 to get the desired result based on the Date in cell H4.

Regards.

Amien

sp_AnswersTopicSeeAnswer See Answer
Avatar
vasim khatik
Member
Members
Level 0
Forum Posts: 26
Member Since:
July 15, 2018
sp_UserOfflineSmall Offline
2
March 20, 2021 - 11:59 pm
sp_Permalink sp_Print sp_EditHistory

="Period "&IF(AND(MONTH(H4)>6,MONTH(H4)<=12),MONTH(H4)-6,IF(AND(MONTH(H4)>=1,MONTH(H4)<=6),MONTH(H4)+6,""))&" - "&YEAR(H4)-1&"/"&RIGHT(YEAR(H4),2)

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
March 21, 2021 - 9:23 am
sp_Permalink sp_Print

Hello,

You can use following formula to get the period number.

=MATCH(1,MONTH(H4)={7,8,9,10,11,12,1,2,3,4,5,6},0)

In short, MATCH function looks for a 1, in other words for the match of the month number and the numbers in sequence, and return the position of the sequence number. March is month 3 and number three is in position 9 in the sequence, so you get 9 as the result.

If you want all this in one formula in C5, just replace the G1 reference with the above formula.

Br,
Anders

Avatar
Amien Attwood
Cape Town
Member
Members
Level 0
Forum Posts: 18
Member Since:
May 8, 2018
sp_UserOfflineSmall Offline
4
March 24, 2021 - 12:31 am
sp_Permalink sp_Print

Thanks Anders and Vasim for the solutions.

Anders: I cannot get your solution to work. I see some curly brackets in there and also tried CTRL+SHFT+ENTER, still not working....

Vasim: Yours seems to do the trick but the financial year in the text string does not change e.g. when it coms to 2021/07/01 (01 July 2021) the financial year must change to 2021/22 and 2022/07/01 (01 July 2022), financial year must change to 2022/23, etc.

Hope we can get it to work.

Regards,

Amien

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
March 24, 2021 - 3:30 am
sp_Permalink sp_Print sp_EditHistory

Hello,

My bad, should have tested the formula on a computer also, worked fine for some reason on Excel app on my tablet.

See attached for an example. The formula now in cell C5 is in this example as following (split in different rows for better readability):
=CONCATENATE(
"Period ",
MATCH(TRUE,MONTH($H$4)={7,8,9,10,11,12,1,2,3,4,5,6},0),
" - "&IF(MONTH($H$4)>6,YEAR($H$4)&"/"&RIGHT(YEAR($H$4)+1,2),YEAR($H$4)-1&"/"&RIGHT(YEAR($H$4),2))
)

Hope this helps you further.

Br,
Anders

P.S. If you are on older version of Excel than 2019 or 365 then you need to use CTRL + SHIFT + ENTER.

sp_AnswersTopicAnswer
Answers Post
Avatar
Amien Attwood
Cape Town
Member
Members
Level 0
Forum Posts: 18
Member Since:
May 8, 2018
sp_UserOfflineSmall Offline
6
March 24, 2021 - 10:21 pm
sp_Permalink sp_Print

Hi Anders

You a real STAR! This time, it works perfectly.

I will unpack the formula so that I can understand the context and practice on my own.

Great stuff!

Regards,

Amien.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
7
March 25, 2021 - 4:54 am
sp_Permalink sp_Print

Hello,

Thanks for the kind words. Just glad I was able to help.

Br,
Anders

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: David, yonatan zelig, Christopher Anderson
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.