ok i am working on a sales history report at work... i have generated a spreadsheet (from accounting software to a .csv file)... i have a column "activity date" adjacent to it i inserted a blank column named "sales year"... our sales year runs 9/01/2009 to 8/31/2010 (sales year 2009-10)... i would like to convert the activity date to sales year.. i have at least a good 6 years of data i want to work from... what i am wanting to see is customer retention and growth... i would then take this raw data and put into a pivot table somehow...
HERE IS LAYOUT OF WHAT I WANT AND DATA I HAVE...
*** sample data is on right and layout options on left... ***
Exel Help
-
dukmisr2005
- Duck South Addict
- Posts: 1579
- Joined: Sun Sep 25, 2005 4:20 pm
- Location: Cleveland, MS
- Contact:
Exel Help
- Attachments
-
- sample history sheet
Last edited by dukmisr2005 on Tue Jan 19, 2010 12:50 pm, edited 1 time in total.
~ Ric M.
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
Re: Exel Help
Are you wanting to change for example 11/05/2009 to 11/2009??
http://safefireshooting.com/
"A free people ought not only to be armed and disciplined, but they should have sufficient arms and ammunition to maintain a status of independence from any who might attempt to abuse them"
-George Washington
"A free people ought not only to be armed and disciplined, but they should have sufficient arms and ammunition to maintain a status of independence from any who might attempt to abuse them"
-George Washington
-
dukmisr2005
- Duck South Addict
- Posts: 1579
- Joined: Sun Sep 25, 2005 4:20 pm
- Location: Cleveland, MS
- Contact:
Re: Exel Help
no i would like 11/02/2009 to be "2009-10"
~ Ric M.
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
Re: Exel Help
not sure what your trying to do, are you wanting Excel to convert the text for you or are you typing something in and Excel is changing the format on you? if you want it to be just text data, put an ' before what you type and it forces the cell format to stay like you type it.
meaning
in the cell now you may type 11/10 and it converts it to 11/10/09 or 11/10/10. If you type '11/10 it will stay like that in the cell as it's typed just without the ' showing.
meaning
in the cell now you may type 11/10 and it converts it to 11/10/09 or 11/10/10. If you type '11/10 it will stay like that in the cell as it's typed just without the ' showing.
"I hear they are developing a new fighter specially for fighting in the middle east. It's called the F-U!" - crow, Aug. 2008
Member FLHC
Lane Romero
Member FLHC
Lane Romero
-
timberhunter
- Regular
- Posts: 36
- Joined: Tue Nov 15, 2005 8:17 am
- Location: Louisville Ms
Re: Exel Help
If you are just wanting the year to show up in the sales year column try. =YEAR() and put the corresponding cell in the parenthesis. It will convert 11/12/2009 to 2009.
Making Dreams Come True One Adventure at a Time
www.breakawayoutdoors.org
www.breakawayoutdoors.org
-
timberhunter
- Regular
- Posts: 36
- Joined: Tue Nov 15, 2005 8:17 am
- Location: Louisville Ms
Re: Exel Help
Ok I think i've got it, You'll have to replace the D2 with whatever cell your activity date is in. =TEXT(YEAR(D2),"0")&" - "&TEXT(YEAR(D2)+1,"0")
Making Dreams Come True One Adventure at a Time
www.breakawayoutdoors.org
www.breakawayoutdoors.org
-
dukmisr2005
- Duck South Addict
- Posts: 1579
- Joined: Sun Sep 25, 2005 4:20 pm
- Location: Cleveland, MS
- Contact:
Re: Exel Help
yeah timber you have the idea.... our 2009-10 sales year runs from Sept 1 (2009) to Aug 31 (2010)... the dates in the activity date column are invoice dates generated by AgriS, our goofed up accounting system that John-Deere uses and sells...
~ Ric M.
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
-
timberhunter
- Regular
- Posts: 36
- Joined: Tue Nov 15, 2005 8:17 am
- Location: Louisville Ms
Re: Exel Help
Ok Here we go =IF(MONTH(D2)<9,TEXT(YEAR(D2)-1,"0")&" - "&TEXT(YEAR(D2),"0"),TEXT(YEAR(D2),"0")&" - "&TEXT(YEAR(D2)+1,"0")) remember to replace the D2's
Making Dreams Come True One Adventure at a Time
www.breakawayoutdoors.org
www.breakawayoutdoors.org
-
dukmisr2005
- Duck South Addict
- Posts: 1579
- Joined: Sun Sep 25, 2005 4:20 pm
- Location: Cleveland, MS
- Contact:
Re: Exel Help
thanks timber... what i wanted....
~ Ric M.
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
Dulaney Seed Inc./AgVenture
877-974-7333
-- Its not the man but the message that keeps on going!! --
-- There is everywhere, but not everywhere is there...
-
timberhunter
- Regular
- Posts: 36
- Joined: Tue Nov 15, 2005 8:17 am
- Location: Louisville Ms
Re: Exel Help
Welcome....... You gave me something to do today
Making Dreams Come True One Adventure at a Time
www.breakawayoutdoors.org
www.breakawayoutdoors.org
Who is online
Users browsing this forum: Amazon [Bot] and 19 guests