INT Formula on Closing Date to Return Year and Quarter

MLSNetworks

New Member
Joined
Jun 6, 2019
Messages
17
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
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!
 
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0
You're right and I see that (the text string aspect).

Thank you so much for all your insight! I greatly appreciate it!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top