Explanation
This formula is uses the WORKDAY function, which returns a date in the future or past, based on start date and required work days. WORKDAY automatically excludes weekends, and can also exclude holidays if provided as a range of dates.
In the example shown, the project end date is in column C, and days is in column D. The end date represents a due date or target date, and days represents duration in work days. In column E, the WORKDAY function calculates a date by which the project must start in order to finish by the end date.
In this case, we need to count backwards from the end date, so we supply days as a negative number. For holidays, we provide the named range “holidays”, which includes the dates in G5:G9. The dates will not be included as workdays.
With a negative value for days, WORKDAY moves in time from the end date, taking into account weekends and holidays, and returns December 19, 2018 as the start date. If holidays are not provided, the same formula returns a start date of December 24.
Note the WORKDAY function does not count the start date as a workday.
Custom workdays
If your schedule requires custom workdays (i.e. weekdays are not Monday-Friday) you can substitute the WORKDAY.INTL function for WORKDAY. For example, to calculate a start date for a project where workdays include Saturday, you could use:
=WORKDAY.INTL(C5,-D5,11,holidays)
WORKDAY.INTL can be configured in many ways. This page provides details .
Explanation
In this example, the goal is to return a number that represents quarter (i.e. 1,2,3,4) for any given date. In other words, we want to return the quarter that the date resides in.
ROUNDUP formula solution
In the example shown, the formula in cell C5 is:
=ROUNDUP(MONTH(B5)/3,0)
The ROUNDUP function works like the ROUND function , except that ROUNDUP will always round numbers 1-9 up to a given number of digits, supplied as the num_digits argument . In this case, because we want to get back an integer, we use zero for num_digits .
Working from the inside out, the MONTH function first extracts the month as a number between 1-12, then divides this number by 3:
=MONTH(B5)/3
=1/3
=0.3333
The result is then rounded up to the nearest whole number using the ROUNDUP function :
=ROUNDUP(0.3333,0) returns 1
The result is 1, since 0.3333 rounded up to the next whole number is 1.
Adding “Q”
If you want the quarter number to include a “Q” you can concatenate the numeric result from ROUNDUP to the “Q”. The formula in D5 is:
="Q"&ROUNDUP(MONTH(B5)/3,0) // returns "Q1"
The result is the letter “Q” prepended to the number:
