Using Excel: calculating quarter periods since a date

Belix · Post by **Belix** » 23 Mar 2007, 11:06

Hi folks
can anyone tell me how to calculate the number of quarter year periods since a certain date? To make it more complicated, the quarters are pegged at certain dates.

quarters are end Feb, end May, end August and end Nov

Eg item bought in jan 2005. how many quarter year periods to end of Feb 06.
Calculating manually would make it 6 quarters - Feb 05, May 05, Aug 05, Nov 05, Feb 06.

Any help appreciated!

Post by **Anthro** » 23 Mar 2007, 11:46

Shoe, I think the person /people to ask are Rustypup / Tribble - I have seen them dabble in that sort of formula
*calls* Rustee !!

Tribble · Post by **Tribble** » 23 Mar 2007, 11:48

Belix wrote:Hi folks
can anyone tell me how to calculate the number of quarter year periods since a certain date? To make it more complicated, the quarters are pegged at certain dates.

quarters are end Feb, end May, end August and end Nov

Eg item bought in jan 2005. how many quarter year periods to end of Feb 06.
Calculating manually would make it 6 quarters - Feb 05, May 05, Aug 05, Nov 05, Feb 06.

Any help appreciated!

Working on it for you. Just getting around the quarter thing. Can use Days360 function to calc days just using If and vlookup to finish it.

Post by **Anthro** » 23 Mar 2007, 11:50

See what I mean - Tribz to da Res-Q

Tribble · Post by **Tribble** » 23 Mar 2007, 12:00

@Belix - what is the oldest date that you need to use? This would make the formula much easier

Belix · Post by **Belix** » 23 Mar 2007, 12:43

Thanks tribble. Oldest date would be 01/03/2003.

SoulBlade · Post by **SoulBlade** » 23 Mar 2007, 13:29

For more Excel queries also head to ozgrid.

Tribble · Post by **Tribble** » 23 Mar 2007, 15:09

@Belix - I mailed you a file. Might be a solution. Have a look and tell me.

Tribble · Post by **Tribble** » 23 Mar 2007, 15:31

Sorry i see that it is not quite right yet. Give me a little more time.

@Belix - sorry my brain just doesn't seem to work anymore. Got you halfway there but it is not 100% perfect. Will continue to work on it this weekend - hopefully have a solution by Monday. If any one else has a solution - I am open to your suggestions

Minoss · Post by **Minoss** » 23 Mar 2007, 17:01

Belix · Post by **Belix** » 24 Mar 2007, 11:16

No rush mate. It's beyond me, so I can hardly rush you now, can I?

Belix · Post by **Belix** » 26 Mar 2007, 10:06

@ Tribble. Thanks for your help. It's almost there. Unfortunately, it does not cut off exactly at the quarter mark. For example is something was bought on 28 Feb 07 it would be one quarter old, whereas if it was bought on 1 Mar 07 it would not be. Must work in that specific quarter.

But much appreciated, you've given me something to start with. Thanks!!!

Tribble · Post by **Tribble** » 26 Mar 2007, 10:12

Belix wrote:@ Tribble. Thanks for your help. It's almost there. Unfortunately, it does not cut off exactly at the quarter mark. For example is something was bought on 28 Feb 07 it would be one quarter old, whereas if it was bought on 1 Mar 07 it would not be. Must work in that specific quarter.

But much appreciated, you've given me something to start with. Thanks!!!

I know and that is where I sit. Working on a vlookup with if functions but it is getting very big. Will send it to you tonight as I am at a client.

Slasher · Post by **Slasher** » 26 Mar 2007, 10:17

Tribble, please also send to me...

Would love to look at the formula you have...

Belix - You can also send me the file that Tribble sent so I can play around with a bit too...

slasher at webmail dot co dot za

Tribble · Post by **Tribble** » 26 Mar 2007, 10:19

Slasher wrote:Tribble, please also send to me...

Would love to look at the formula you have...

Belix - You can also send me the file that Tribble sent so I can play around with a bit too...

slasher at webmail dot co dot za

Will do this evening - at a client and not have access to my stuff.

Slasher · Post by **Slasher** » 26 Mar 2007, 10:23

Thanks... Mucho Gracias...

As I also said to Belix - If you send it to me I will also mess around. Nothing to do here at work... Could maybe find a simple solution... I just need to see what Tribble already has to work from there. Too lazy to do a complete rewrite of code that determines WHEN the quarters are...

If I have that as a list it is quick and painless..

doo_much · Post by **doo_much** » 26 Mar 2007, 10:31

Hmm, this sound interesting.
Tribble please add me to that mailing list?

doo dot much at gmail dot com

Slasher · Post by **Slasher** » 27 Mar 2007, 12:24

Belix -- I HAVE IT!

Howeever, a few questions...

1) Till what year should it calculate, my setup is good till about 2090

2) Do you want it in a nice neat form?

3) My logic is that if the quarter is bigger or equal to the 'past date' and smaller and equal to the 'current date' it counts it. Is this correct?

Im seriously hoping you dont want to calculate more than one at a time, as mine is designed around entering one date and it gives you a count of quarters. Do you want it to show you the quarters as well or not (Mine does NOT)

Slasher

Belix · Post by **Belix** » 27 Mar 2007, 12:53

Hey hey hey
1 - 2090 is plenty!
2 - however I can get it! I'd like to be able to 'look under the hood' to see how you calculated it if possible though.
3 - I'd have to see it in action. I have to do calculations on amounts in certain quarters that depreciate by a set amount each quarter. So either I have a LOOONG tabel carrying the amount from quarter to quarter, or I can work out the number of quarters and multiple by the depreciation percentage. sounds vague - does that make any sence?
Have a whole pile of items which I need to work out the quarters for...so, er...

Slasher · Post by **Slasher** » 27 Mar 2007, 12:56

Well, i mailed it to you, tribble and doomuch. Protected AND unprotected versions... Any questions on logic, let me know...

I am unfortunately at the moment a litttttle unable to make it so you can paste in an array of dates...

My setup just calculates the quarters between given date and current date and gives an amount... Fiddle with it however much further you want

Afflict · Post by **Afflict** » 27 Mar 2007, 13:19

Can you add me to the mailing list

I dabble in excel, love to see what you did. I like to watch the diff formulas of excel in action.

Slasher · Post by **Slasher** » 27 Mar 2007, 13:25

Send me a pm or something with you email...

Its a biggish file at 700kb, I know, but I love working through tabled data, which takes up some space...

Belix · Post by **Belix** » 29 Mar 2007, 16:19

Sorry folks. Have had a hectic today. Will have a look over tonight.
Thanks for the continued efforts.

PCFormat Forum Archive

Using Excel: calculating quarter periods since a date

Using Excel: calculating quarter periods since a date

Re: Using Excel: calculating quarter periods since a date