Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Paste Financial Years | Excel Worksheet Functions | |||
Financial Years | Excel Discussion (Misc queries) | |||
Copy and paste financial formulas | Excel Worksheet Functions | |||
URGENT IF STATEMENT REQUIRED:True if moving between financial years? | Excel Worksheet Functions | |||
year function and financial years | Excel Worksheet Functions |