Set cell to first day of the financial year

[jaguar23]

I have a column that has many different dates in it (Column A). Based on the date in that column I want Column B to show the first date of the financial year that Column A falls into.

For instance, if cell A1 shows 13/07/2010 I want cell B2 to show 01/07/2010.

What formula would I use?

Thanks!

 

[Jonmo1]

I'm assuming your definition of financial year is July 1st to June 31st...

Try

=LOOKUP(A1,DATE(ROW(1900:2100),7,1))

 

[wangyuhu]

B2="01"&MID(TEXT(A1,"dd/mm/e"),3,8)
++++++++
B2=TEXT(DATE(YEAR(A1),MONTH(A1),1),"dd/mm/e")

 

[jaguar23]

I'm assuming your definition of financial year is July 1st to June 31st...

Try

=LOOKUP(A1,DATE(ROW(1900:2100),7,1))

Thanks. However I slightly changed it to include the dollar signs ($) for the ROW reference as the formula was copied down for various rows.

Another quick one - how would I then get it to display the last day of the finacial year?

 

[facethegod]

first day

=DATE(YEAR(A1)-1,MONTH(A1)+MOD((6-MONTH(A1)),12),31)

Last day

=DATE(YEAR(A1),MONTH(A1)+MOD((6-MONTH(A1)),12),30)

HTH

 

[Jonmo1]

Try

=EDATE(LOOKUP($A1,DATE(ROW(1900:2100),6,30)),12)

EDATE requires the Analysis toolpack from Tools - Addins.
This is already installed in XL2007 and higher

Also, for performance improvement, you might change the
1900:2100 to numbers reflecting the earliest and latest years that might appear in your dataset, like 1980:2020 or something like that.
The fewer rows in the reference the better.


Hope that helps.

 

[jaguar23]

Thanks all. I ended up using the following for the last day of the financial year:

=DATE(YEAR(A1)+1,6,30)

 

[facethegod]

Thanks all. I ended up using the following for the last day of the financial year:

=DATE(YEAR(A1)+1,6,30)

If your date is 4/12/2010 your result will return 6/30/2011. I would expect 6/30/2010.

Please advise

 

[jaguar23]

If your date is 4/12/2010 your result will return 6/30/2011. I would expect 6/30/2010.

Please advise

I meant to write it as cell B1 rather than A1.

So my final data looks like this:

A1: 13/07/2010

B1: =LOOKUP(A1,DATE(ROW(1900:2100),7,1)) - this produces 1/07/2010

C1: =DATE(YEAR(B1)+1,6,30) - this produces 30/06/2011