| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing dates
I have a number of dated enteries in a spreadsheet. As our Financial year
starts April 1st and ends March 31st following year I would like to reference the enteries by financial year. Fianancial I have dates in column A and would like to reference them by financial year in column B A B 21/04/06 06/07 17/10/05 05/06 13/08/06 06/07 17/03/06 05/06 Is this possible -- pk |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing dates
If the date is in A1, then:
=IF(A1<38808,"05/06","06/07") This will work for all dates in fiscal years 2005 and 2007. Clearly it will NOT work outside of that. -- Gary''s Student - gsnu200752 "PK" wrote: I have a number of dated enteries in a spreadsheet. As our Financial year starts April 1st and ends March 31st following year I would like to reference the enteries by financial year. Fianancial I have dates in column A and would like to reference them by financial year in column B A B 21/04/06 06/07 17/10/05 05/06 13/08/06 06/07 17/03/06 05/06 Is this possible -- pk |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing dates
Sample TAble
Date Year Start Values Sumary Totals 21/04/2006 2006 12.00 2005 46 17/10/2005 2005 34.00 2006 45 13/08/2006 2006 45.00 17/03/2006 2005 12.00 Formula to extract year start in cell B3: =IF(AND(A3=DATE(YEAR(A3),4,1),A3<=DATE(YEAR(A3)+1 ,3,31)),YEAR(A3),YEAR(A3)-1) Formula in Cell F3 =SUMPRODUCT(--(B3:B6=E3)*(C3:C6)) Years entered as text in column E Will this help? Peter "PK" wrote: I have a number of dated enteries in a spreadsheet. As our Financial year starts April 1st and ends March 31st following year I would like to reference the enteries by financial year. Fianancial I have dates in column A and would like to reference them by financial year in column B A B 21/04/06 06/07 17/10/05 05/06 13/08/06 06/07 17/03/06 05/06 Is this possible -- pk |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing dates
Sorry I forgot to make absolute the ranges for F3. Should have been
=SUMPRODUCT(--($B$3:$B$6=E3)*($C$3:$C$6)) Which makes the values correct. Peter "Billy Liddel" wrote: Sample TAble Date Year Start Values Sumary Totals 21/04/2006 2006 12.00 2005 46 17/10/2005 2005 34.00 2006 45 13/08/2006 2006 45.00 17/03/2006 2005 12.00 Formula to extract year start in cell B3: =IF(AND(A3=DATE(YEAR(A3),4,1),A3<=DATE(YEAR(A3)+1 ,3,31)),YEAR(A3),YEAR(A3)-1) Formula in Cell F3 =SUMPRODUCT(--(B3:B6=E3)*(C3:C6)) Years entered as text in column E Will this help? Peter |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing dates
There's probably a simpler way, but try:
=IF(MONTH(A1)<4,TEXT(MOD(YEAR(A1)-1,100),"00")&"/"&TEXT(A1,"yy"),TEXT(A1,"yy")&"/"&TEXT(MOD(YEAR(A1)+1,100),"00")) -- David Biddulph "PK" wrote in message ... I have a number of dated enteries in a spreadsheet. As our Financial year starts April 1st and ends March 31st following year I would like to reference the enteries by financial year. Fianancial I have dates in column A and would like to reference them by financial year in column B A B 21/04/06 06/07 17/10/05 05/06 13/08/06 06/07 17/03/06 05/06 Is this possible -- pk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| referencing a different tab by referencing a list in the current s | Excel Worksheet Functions | |||
| how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
| Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel | |||
Hybrid Mode
