If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
copy drag financial years
Hi i use financial years a lot and was wondering if there was a simple way to
copy, paste/drag the years down a column so they expand? For example when i drag the years down i get: 2006/07 2006/08 2006/09 Does anyone know how to get 2006/07, 2007/08 etc? Thanks |
#2
|
|||
|
|||
copy drag financial years
I would do it with a formula. Enter your starting fiscal year in, say, A1,
then use: =left(a1,4)+1&"/"&text(right(a1,2)+1,"00") and drag down. Regards, Fred "Ben" wrote in message ... Hi i use financial years a lot and was wondering if there was a simple way to copy, paste/drag the years down a column so they expand? For example when i drag the years down i get: 2006/07 2006/08 2006/09 Does anyone know how to get 2006/07, 2007/08 etc? Thanks |
#3
|
|||
|
|||
copy drag financial years
Try the below.
=TEXT(DATE(2006,ROW(A6),1),"yyyy/mm") -- Jacob "Ben" wrote: Hi i use financial years a lot and was wondering if there was a simple way to copy, paste/drag the years down a column so they expand? For example when i drag the years down i get: 2006/07 2006/08 2006/09 Does anyone know how to get 2006/07, 2007/08 etc? Thanks |
#4
|
|||
|
|||
copy drag financial years
Perhaps Jacob intended to say =TEXT(DATE(2000+ROW(A6),ROW(A7),1),"yyyy/mm")
? But that would only go as far as 2011/12 before going wrong. It may therefore need =2000+ROW(A6)&"/"&TEXT(ROW(A7),"00") which would be OK to 2098/99 or =2000+ROW(A6)&"/"&TEXT(MOD(ROW(A7),100),"00") if you want to go further. -- David Biddulph "Jacob Skaria" wrote in message ... Try the below. =TEXT(DATE(2006,ROW(A6),1),"yyyy/mm") -- Jacob "Ben" wrote: Hi i use financial years a lot and was wondering if there was a simple way to copy, paste/drag the years down a column so they expand? For example when i drag the years down i get: 2006/07 2006/08 2006/09 Does anyone know how to get 2006/07, 2007/08 etc? Thanks |
#5
|
|||
|
|||
copy drag financial years
Hi Ben,
Hi i use financial years a lot and was wondering if there was a simple way to copy, paste/drag the years down a column so they expand? For example when i drag the years down i get: 2006/07 2006/08 2006/09 You have received a few formula based approaches, another way of doing this is by using custom lists. Set up a custom list in Excel Options with the financial years. Now enter a financial year in a cell. Click on the little dot at the corner of the cell selector and drag. Financial years fill as expected. Ed Ferrero www.edferrero.com |
Thread Tools | |
Display Modes | |
|
|