Mon 18 Jul 2016

Solution - Challenge 45 – Number of Days Passed in a Quarter

By |Monday, July 18th, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 45 - Number of Days Passed in a Quarter

The formula to calculate number of days passed in a quarter is

=A1-DATE(YEAR(A1),(ROUNDUP(MONTH(A1)/3,0)-1)*3+1,1)

Sat 14 Jun 2014

Tips & Tricks 12 - Converting Date to a Quarter

By |Saturday, June 14th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is 1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4.

=CEILING(MONTH(A1)/3,1)

OR

= ROUNDUP(MONTH(A1)/3,0)