0

I need a way to determine how much of a given period (start and end dates held in separate columns) falls within a financial year (1/1/XXXX - 31/12/XXXX). Can anyone help me to determine the number of months in that period that falls in the financial year.

I found a similar question to determine the number of days - is there a way to convert this to months? Start Date in A2 and End Date in B2 =MAX(0,MIN($B2,DATE(2019,12,31))-MAX($A2,DATE(2019,1,1))+1)

Really appreciate the help!

  • the number of days - is there a way to convert this to months? No way. The relation between days and months is not a constant. Month can contain from 28 till 31 day (on the Earth). Approximate value can be obtained while extract month numbers from dates, then substract. Maybe additionally extract day numbers, and substract 1 additionally if starting day number is greater then finishing day number. – Akina Sep 18 '19 at 5:38
0

This method will help you to find number of months between two dates in context of Financial Year.

enter image description here

How it works:

  • Date format is YYYY/MM/DD.
  • Formula in Cell L75:

    =IF(AND(K72>=DATE(2019,4,1),L72<=(DATE(2020,3,31))),DATEDIF(K72,L72,"M"),"")
    

N.B.

  • Used data set is for the Asia region, FINANCIAL YEAR (From 1st April to 31st March).

  • You need to change it according to popular practice in your region.

  • For European countries is, 1st January to 31st December.

  • Date Format is editable, pick one which suits your need.

Adjust cell references in the formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.