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)`

thanks!

@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.

Ah, this is so helpful, @Jeff_Hager. Didnt know about ceilings and floors and stuff. Now I do and understand. Thank you!

They stop your walls from falling down

Jeff is my ceiling and my floor!

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.