CODEWITHSUNDEEP
excel-formula
Glss1
Glss1

Reputation: 45

Excel help getting financial year from date - Many attempts

Spent a very long time trying to do this. I have a date column. I want to create a column for the financial year the date is in…

Date (columnA) Year (columnB) (what I want)
01/01/2026 2026
01/08/2027 2028
03/04/2029 2029

I have changed the format to ‘date’ and ‘general’ but no difference.

I even have tried to split the equations out into two parts (the first one gets the month), and the second one adds a year based on whether that month column is over ‘7’ but it just takes the year from the date column again.

Additional things I have tried:

Various attempts I have made…

=IF((MONTH(A14<7)),(YEAR(A14)),(YEAR(A14)+1))

=IF((MONTH(A22>7)),((YEAR(A22+1))),(YEAR(A22)))

-some are right, (shifts but some are incorrect)

=TEXT(A10,"YYYY") + ((TEXT(A10, "MM") >= (TEXT(7,"MM"))))

It just adds one to the years, no matter what

Hope someone can help me out here with this, Thanks

Upvotes: 0

Views: 1180

Answers (2)

P.b
P.b

Reputation: 11468

In your attempts I noticed the following:

MONTH(A14>7)

A data value is stored as a number starting from 1/1/1900 being 1. Every date over Januar 7th 1900 will be greater than 7.

If you would write it this way =MONTH(A14)>7 it checks for the number of the month first and then checks if that's greater than 7.

So =IF(MONTH(A14)>7,YEAR(A14),YEAR(A14)+1)

Or =YEAR(A14)+(MONTH(A14)>7)

Upvotes: 1

Michael R
Michael R

Reputation: 11

It looks like you have parenthesis at wrong place.

Your formula: =IF((MONTH(A14<7)),(YEAR(A14)),(YEAR(A14)+1))

Refer to what you say, looks like you need: =IF((MONTH(A14)<7),(YEAR(A14)),(YEAR(A14)+1))

MONTH(A14) return number of month and if it less than 7 than it returns year representation of date in A14

example

Upvotes: 0

Related Questions