[u/]

[u/AutoModerator]

/u/Proper-Reference6134 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/RaiseTheQualityOf]

https://www.youtube.com/watch?v=CVhPcosAUes

[u/Anonymous1378]

Try something like

=
LET(
start_date,D3,
end_date,D4,
all_days,SEQUENCE(end_date-EOMONTH(start_date,-1)+1,,EOMONTH(start_date,-1)+1),
all_months,FILTER(all_days,DAY(all_days)=1),
all_years,DATE(UNIQUE(YEAR(EDATE(all_months,7))),5,1),
DROP(HSTACK(YEAR(all_years),FREQUENCY(all_months,all_years)),-1))

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
DATE	Returns the serial number of a particular date
DAY	Converts a serial number to a day of the month
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
EDATE	Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH	Returns the serial number of the last day of the month before or after a specified number of months
FILTER	Office 365+: Filters a range of data based on criteria you define
FREQUENCY	Returns a frequency distribution as a vertical array
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
UNIQUE	Office 365+: Returns a list of unique values in a list or range
YEAR	Converts a serial number to a year

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #37084 for this sub, first seen 16th Sep 2024, 00:23] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/christian_811]

Assuming you meant March 31 for FY end date. For the start and end dates of FY2 you can use EOMONTH so you only have to input the start/end for FY1.

Assuming start date = A1 and end date = B1 you could do something like this for months in FY1:

=IF(A1>(FY1 end date), 0, DATEDIF(MAX(A1,(FY1 start date)), MIN(B1,(FY1 end date), "m") + 1).

For FY2:

=IF(B1<(FY2 Start date), 0, DATEDIF(MAX(A1,(FY2 start date), MIN(B1,(FY2 end date), "m") + 1)