Calculate current quarter in Excel

[bmedis]

how can I have the current calendar year quarter automatically show from a date in a cell - not from a table? I have a 2 week pay period and need to show the current quarter that pay period is in.....so if cell L24 has "10/3/2003" for next pay date in it what formula could I use to show that 10/3/2003 is in the 4th quarter?

Thanks!

 

[jimmidopolus]

Can think of couple of ways depending on the way your quarters start.

If they start always first of the month, then a simple ROUNDUP(MONTH(L24),0) formula would work.

If the Quarters overlap months then use the date codes. For example, we know 1/1/03 is displayed from 37622. Therefore it is possible to write a nested IF statement to determine which quarter a date falls in by what values its date code falls between. Subsequently...

(Assume Q1 starts 12/30/02, Q2 3/31/03, Q3 6/30/03, Q4 9/29/03)

Statement is..

=IF(L24<=37710,1,IF(L24<=37801,2,IF(L24<=37892,3,IF(L24<=37983,4,&quot;&quot)))

Hope was of some help.

Kind Regards

jimmid

 

[bmedis]

Thank you so much! Worked perfectly!

 

[bmedis]

One thing.....confused.......where did you get &quot;1/1/03 is displayed from 37622&quot;.........do not understand where you got the 37622.

Thank you!

 

[jimmidopolus]

OK don't know what you know so am just going to try and fully explain. I am no expert though as you can see from the lack of stars!

When a date is typed into Excel, it is converted to a number and then the number in the cell is formatted to a Date format. You example, if the default date format is mm/dd/yy (the default is set by the Regional Settings in The Control Panel), when you type in 1/1/03, the cell value is converted to 37622 and it is formatted to 01/01/03. The cell value remains 37622 and can be viewed by changing the cell format property (e.g. to General).

The Date system default in Excel is the 1900 system, so 1/1/1900 is represented by the number 1. Then it increases by 1 for each day. So today is 09/27/03 (I'm in England!) so it is 37891, divide that by 365 and you get approx 103 is how many years has roughly passed.

If times are entered, they are represented as a decimal.

Date cells therefore are numbers and not strings, which can be pain sometimes so used precede the entry with ' to force the text property.

Great inquisitiveness. Its always good to understand how things work rather than just using them, which this site allows rather well.

Regards

jimmid

 

[bmedis]

Got it........very interesting! I am probably an intermediate user.........if I do not know I can usually figure out...I do know what I do NOT know, though, and always ask for help. I am in Delaware in the US, and want to thank you again for taking the time to respond.

Thanks again!

 

[DrBowes]

This would work for any year, there are no doubt better solutions.

=IF(month(L24)<=3,1,IF(month(L24)<=6,2,IF(month(L24)<=9,3,IF(month(L24)<=12,4,&quot;Error&quot)))

 

[DrBowes]

=INT(MONTH(E11)/3)+1 would be more elegant.

 

[bmedis]

Thank you all.......so nice to have options.

 

[jimmidopolus]

Think INT(MONTH(E11)/3)+1 will cause errors as december will be month 12/3=4+1=5.

I meant to write earlier but mis-typed:

ROUNDUP(MONTH(L24)/3,0)

sorry for my errors

james

Regards

james