INT Formula on Closing Date to Return Year and Quarter

MLSNetworks

New Member
Joined
Jun 6, 2019
Messages
15
Hello Friends,

I received an excel file with the following INT formula which I've never seen before and I was hoping someone could explain to me why this was used.

The formula is =YEAR(A3) & "-" & "Q" &INT((MONTH(A3)+2)/3) and it is reading from a date in US format m/dd/yyyy (formatted in Format Cells as Date)

I understand first half of the formula but I don't understand why you would use INT (Integer) with the last part +2) /3)

Can someone explain to me?

Thank you!

1617391044405.png
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MLSNetworks

New Member
Joined
Jun 6, 2019
Messages
15
Hi jtakw,

Thank you for your reply. I did exactly what you did and I do see the logic. So using INT is rounding down to the nearest whole number and then dividing the number by 3 to give you the quarter in a year (12 months).

I guess it easier to use a formula like this rather than finding the quarter from a vlookup which is what I have been doing and putting the quarter in its own column.

Thanks again!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,097
Office Version
  1. 2016
Platform
  1. Windows
Hi jtakw,

So using INT is rounding down to the nearest whole number and then dividing the number by 3 to give you the quarter in a year (12 months).

Thanks again!

You're welcome, thanks for the feedback.

But, no, it's the Other way around, you Add 2 to the Month, Divide the result by 3, Then take the Integer After the division to get the Quarter.
 
Solution

MLSNetworks

New Member
Joined
Jun 6, 2019
Messages
15

ADVERTISEMENT

Yes, got it. Hence the parenthesis and the order of operations.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,097
Office Version
  1. 2016
Platform
  1. Windows
I guess it easier to use a formula like this rather than finding the quarter from a vlookup which is what I have been doing and putting the quarter in its own column.

There's nothing wrong with how you've been doing it, having the Quarter in it's Own column as a Real number makes it a lot easier to do additional math/comparison.

The formula in your OP, results as a Text string, which makes it difficult to manipulate in math/comparisons.
 

MLSNetworks

New Member
Joined
Jun 6, 2019
Messages
15
You're right and I see that (the text string aspect).

Thank you so much for all your insight! I greatly appreciate it!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,116
Messages
5,768,211
Members
425,459
Latest member
Danniey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top