How do I calculate the present week number with a Math column?
And is it possible to calculate a Quarter as well?
This to get the week number:
WEEKNUM(date)
Something like this to get the quarter:
CEILING(MONTH(date)/3)
3 Likes
thanks!
1 Like
@Jeff_Hager Is there somewhere you know of where I can find the logic behind something like CEILING(MONTH(date)/3)? It works, but I’d like to understand it 
You’ll have to crawl inside my head.
First of all, you take the number of the month MONTH(date) and divide it by 3. That’s because there are 3 months per quarter and we want to get a result that’s between 1 and 4.
The result will include a decimal if the division isn’t perfect. For example let’s take February. 2/3=0.666 Obviously 0.666 isn’t a valid quarter number so we use CEILING to always round the number up. January (1/3) would equal 0.333, so in that case we don’t want to round down to 0. With CEILING, it will still round up to 1. For March (3/3) it is evenly divisible by 3 and gives you 1. For July (7/3) you get 2.333 which rounds up to 3 using CEILING. For December (12/3) it is evenly divisible with a result of 4.
ROUND will round up or down depending on the decimal value.
CEILING will always round up.
FLOOR will always round down.
By default, ROUND will round to the nearest whole number, but you can also specify the number of decimal places to consider for your rounding by adding a comma and the number of decimal places. For example ROUND(1.56, 1) will round up to 1.6 whereas ROUND(1.54, 1) will round down to 1.5. CEILING and FLOOR on the other hand, will only round to a whole number, so if you needed to include decimals in CEILING or FLOOR math, you would have to use some tricks to multiply the value by 10 (for 1 decimal place, or 100 for 2 decimal places, etc.), do the rounding, then divide that result by 10.
Overall, when you break it down, it’s a pretty simple formula. Divide the month number by the number of months in a quarter, then round that number up to the nearest whole number.
6 Likes
Ah, this is so helpful, @Jeff_Hager. Didnt know about ceilings and floors and stuff. Now I do and understand. Thank you!
1 Like
They stop your walls from falling down 
4 Likes
Jeff is my ceiling and my floor!
2 Likes
1 Like
This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.