How do I convert a date to a fiscal year?
 

I want to convert the dates of tasks to the fiscal year they were completed.
The fiscal year we operate under is July 1 to June 30. In my spreadsheet the
Date Completed is column G and shows for example 6/16/2007 and I want a new
column to reflect just the fiscal year which would be FY07 in this case. A
completed date of 8/15/2007 would be FY08. Appreciate any help.

How do I convert a date to a fiscal year?
 

I think this will give you what you want...

="FY"&TEXT(TEXT(G2,"yy")+(MONTH(G2)6),"00")

Assuming your data starts in Row 2, put this formula in Row 2 of your new
column and copy down.

--
Rick (MVP - Excel)


"Bret" wrote in message
...
I want to convert the dates of tasks to the fiscal year they were
completed.
The fiscal year we operate under is July 1 to June 30. In my spreadsheet
the
Date Completed is column G and shows for example 6/16/2007 and I want a
new
column to reflect just the fiscal year which would be FY07 in this case.
A
completed date of 8/15/2007 would be FY08. Appreciate any help.

How do I convert a date to a fiscal year?
 

That worked perfect. Now my next question. How do I calculate totals for
each FY since their intermixed in the same column?

"Rick Rothstein" wrote:

I think this will give you what you want...

="FY"&TEXT(TEXT(G2,"yy")+(MONTH(G2)6),"00")

Assuming your data starts in Row 2, put this formula in Row 2 of your new
column and copy down.

--
Rick (MVP - Excel)


"Bret" wrote in message
...
I want to convert the dates of tasks to the fiscal year they were
completed.
The fiscal year we operate under is July 1 to June 30. In my spreadsheet
the
Date Completed is column G and shows for example 6/16/2007 and I want a
new
column to reflect just the fiscal year which would be FY07 in this case.
A
completed date of 8/15/2007 would be FY08. Appreciate any help.

How do I convert a date to a fiscal year?
 

One way is to sum the whole column, and subtract what you don't want as in:

=sum(a:a)-sumif(a:a,"<"&startdate)-sumif(a:a,""&enddate)

Regards,
Fred.

"Bret" wrote in message
...
That worked perfect. Now my next question. How do I calculate totals for
each FY since their intermixed in the same column?

"Rick Rothstein" wrote:

I think this will give you what you want...

="FY"&TEXT(TEXT(G2,"yy")+(MONTH(G2)6),"00")

Assuming your data starts in Row 2, put this formula in Row 2 of your new
column and copy down.

--
Rick (MVP - Excel)


"Bret" wrote in message
...
I want to convert the dates of tasks to the fiscal year they were
completed.
The fiscal year we operate under is July 1 to June 30. In my
spreadsheet
the
Date Completed is column G and shows for example 6/16/2007 and I want a
new
column to reflect just the fiscal year which would be FY07 in this
case.
A
completed date of 8/15/2007 would be FY08. Appreciate any help.

How do I convert a date to a fiscal year?
 

Assuming the values you want to total are in, say, Column H, and the Fiscal
Year formula I posted is in Column L, try this formula...

=SUMIF(L:L,"=FY07",H:H)

or, if you have unrelated data located in those columns after the data, use
this version where you specify the ranges...

=SUMIF(L2:L100,"=FY07","H2:H100)

--
Rick (MVP - Excel)


"Bret" wrote in message
...
That worked perfect. Now my next question. How do I calculate totals for
each FY since their intermixed in the same column?

"Rick Rothstein" wrote:

I think this will give you what you want...

="FY"&TEXT(TEXT(G2,"yy")+(MONTH(G2)6),"00")

Assuming your data starts in Row 2, put this formula in Row 2 of your new
column and copy down.

--
Rick (MVP - Excel)


"Bret" wrote in message
...
I want to convert the dates of tasks to the fiscal year they were
completed.
The fiscal year we operate under is July 1 to June 30. In my
spreadsheet
the
Date Completed is column G and shows for example 6/16/2007 and I want a
new
column to reflect just the fiscal year which would be FY07 in this
case.
A
completed date of 8/15/2007 would be FY08. Appreciate any help.

How do I convert a date to a fiscal year?
 

=MyDate+184
Formatted as "FY"[$-409]yy