Date Formatting in Excel 1

[kidnos]

Happy new year everyone. I have an issue with some dates I am using in excel. I have a two rows of data (dates) that read as follows:

Mar Jun Sept Dec
1Q02 2Q02 3Q02 4Q02

I would like to be able to say to excel take the last date and go forward or backwards 1. So 2Q02 -1 = 1Q02 ; Or minus 3 months to get 1Q02. However I cant seem to figure out how. I thought about changing it to this:

Mar Jun Sept Dec
2002 2002 2002 2002
But it still wont let me adjust the month because it adds days and eventually it messes up (3/30/2002 - then I have to figure out how many days to add to get to June 30).
Is there a fix to this anyone can think of? Thanks

 

[xlbo]

You do not have dates. you have text entries that correspond in your mind to dates

How are you trying to "go forward" or "go back" ? a SUM, using OFFSET, VLOOKUP? what is the requirement that this is for ? The best way to store you quarters will be dependant on what specifically you wish to do with them.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Andrzejek]

I am trying to get your calculation straight:
If you are calculating on the BLUE numbers, then

[blue]2[/blue]Q02 - [blue]1[/blue] = [blue]1[/blue]Q02

But "Or minus 3 months to get 1Q02"

[blue]2[/blue]Q02 - [blue]3[/blue] = [blue]-1[/blue]Q02

Which one is it?


Have fun.

---- Andy

 

[kidnos]

you are right, it is text, not dates. My ultimate goal is to have a sheet where I can list my date:
Jun
2Q02
and then have it automatically pull in the previous quarter as well as same period in the previous year:
Mar Jun
1Q02 2Q01

The format of the sheet it would pull from is:
Mar Jun Sept Dec FY
1Q06 2Q06 3Q06 4Q06 2006

There would probably be a way to do a lookup or just count 4 columns back but the FY 2006 number would mess it up.
Thanks

 

[Welshbird]

I'd list dates as proper dates and then use this function to determine which quarter it is:

=ROUNDUP(MONTH(A1)/3,0)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[xlbo]

Are you going to be "pulling in" using code or formulae?

I am assuming that you want to pick a Quarter from a dropdown list (or similar) and have 2 other cells populate with the relevant quarters as per your spec ?

If that is the case, then you can probably just use MATCH (to find the selected quarter in your list) and then OFFSET to move the slection (as you have alluded to in your previous post). All that would need to be added would be an IF statement to check that the 2nd character of the cell is Q - if not, move along one more...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Hi,

you will need to store your dates as actual dates. Then you can use the formula EOMONTH to get to anywhere you want forward or backward.

Format = "DD/MM/YYYY"

say 01/01/2008 is in B1 then use =EOMONTH(B1,-2)+1 to get 01/12/2007 and =EOMONTH(B1,0)+1 to get 01/02/2008.

I think you will need to install the Analysis Toolpak to get this function.

Cheers,

Roel

 

[kidnos]

Great, thanks a lot for the help.