Sat 03 Jun 2017

Tips & Tricks 160 - When is Labor Day in a Given Year

By |Saturday, June 03rd, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

While 1st May is celebrated as Labour Day in most of the countries but in USA/Canada, it is celebrated on 1st Monday of September.

Suppose the year is given in cell A1, you can use following formula to calculate the date for Labor Day

=CEILING(DATE(A1,9,1)-2,7)+2

Note - This utilizes the knowledge gained in Article 34 - Show Date for a Coming or Previous Day (Say Coming Monday, Previous Friday)

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)