Using Excel: calculating quarter periods since a date
Using Excel: calculating quarter periods since a date
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!
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!
| Intel C2D E7300 | Asus Striker Extreme | CL X-Fi ME | Asus 8800GTS | Aopen 700W |
| 2*Seagate 1TB Raid | Samsung 2232GW 22" LCD | Team Extreem 2GB DDR2 800 |
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Using Excel: calculating quarter periods since a date
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.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!
For more Excel queries also head to ozgrid.
Core i5 3550 | 8GB RAM | 500W | Samsung T260 | GTX760 OC | 4.56TB HDD space
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
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
@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
Last edited by Tribble on 24 Mar 2007, 09:56, edited 1 time in total.
@ 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!!!
But much appreciated, you've given me something to start with. Thanks!!!
| Intel C2D E7300 | Asus Striker Extreme | CL X-Fi ME | Asus 8800GTS | Aopen 700W |
| 2*Seagate 1TB Raid | Samsung 2232GW 22" LCD | Team Extreem 2GB DDR2 800 |
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
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.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!!!
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
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
My BF2142 Stats:
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
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..
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..
My BF2142 Stats:
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
-
- Registered User
- Posts: 26022
- Joined: 13 May 2004, 02:00
- Location: Getting there...
- Contact:
Hmm, this sound interesting.
Tribble please add me to that mailing list?
doo dot much at gmail dot com
Tribble please add me to that mailing list?
doo dot much at gmail dot com
MOOD - Thirsty
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
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
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
My BF2142 Stats:
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
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...
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...
| Intel C2D E7300 | Asus Striker Extreme | CL X-Fi ME | Asus 8800GTS | Aopen 700W |
| 2*Seagate 1TB Raid | Samsung 2232GW 22" LCD | Team Extreem 2GB DDR2 800 |
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
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
My BF2142 Stats:
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
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...
Its a biggish file at 700kb, I know, but I love working through tabled data, which takes up some space...
My BF2142 Stats:
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za
Slasher : Former member of www.PCFormat.co.za
I have reached the end of my near 5 year forum life. Farewell good days...
slasher (at) webmail (dot) co (dot) za