Calculating dates for subsequent years

[dpaton05]

I have a table that is used for a sheet for generating quotes and every line of the quote table has a cell or column where the date is entered (This cell is A17 or column A). Then there is a cell next to it that calculates if the date is a weekday, weekend or public holiday (This cell is B17). It has the formula =IF(A17="","",IF(COUNTIF(Sheet2!G38:G48,A17),"Public Holiday",IF(WEEKDAY(A17,2)>5,"Weekend","Mon-Fri"))).

The holidays are calculated as follows. In sheet2!G39:G49, there is a list of the public holidays for the current year. Here is a list of formulas in those cells.

G36: =NOW()
G37: =YEAR(G36)
G38: =DATE(G37,1,1)
G39: =DATE(G37,1,26)
G40: =FLOOR(DAY(MINUTE(G37/38)/2+56)&"/05/"&G37,7)-34
G41:=G40-1
G42: =G40-2
G43: =G40+1
G44: =DATE(G37,4,25)
G45: =DATE(G37,6,1+7*2)-WEEKDAY(DATE(G37,6,8-2))
G46: =DATE(G37,10,1+7*1)-WEEKDAY(DATE(G37,10,8-2))
G47; =DATE(G37,12,25)
G48: =DATE(G37,12,26)

This works perfectly for calculating the date to be a public holiday in the current year but my boss wants to be able to complete a quote with dates of activities relating to the quote in a subsequent year. If I use the current cells for calculating the date, it won't work.

I need to be able to enter a line in the quote that has a date in the current year but on the next line, enter a date for the following year or any subsequent year and have it work out if the date is a public holiday, weekday or weekend.

Thanks,
Dave

 

[Dave Patton]

Excel 2010
	A	B
17	1-Sep-18	Weekend
18	25-Dec-18	Public Holiday
19	6-Sep-18	Mon-Fri
20	25-Dec-19	Public Holiday
Sheet2

Cell Formulas
Range	Formula
B17	=IF(A17="","",IF(COUNTIF(Sheet2!$G$38:$G$60,A17),"Public Holiday",IF(WEEKDAY(A17,2)>5,"Weekend","Mon-Fri")))
B18	=IF(A18="","",IF(COUNTIF(Sheet2!$G$38:$G$60,A18),"Public Holiday",IF(WEEKDAY(A18,2)>5,"Weekend","Mon-Fri")))
B19	=IF(A19="","",IF(COUNTIF(Sheet2!$G$38:$G$60,A19),"Public Holiday",IF(WEEKDAY(A19,2)>5,"Weekend","Mon-Fri")))
B20	=IF(A20="","",IF(COUNTIF(Sheet2!$G$38:$G$60,A20),"Public Holiday",IF(WEEKDAY(A20,2)>5,"Weekend","Mon-Fri")))

Just copy your range G37:G48 below G40 and change G49 to 2019
edit your range to G37:G60
Repeat if necessary

 

[dpaton05]

I just worked out how I can do it. In addition to the way above, I have added the formula in H37 to be G37+1 and then just filled the year across. I have filled across about a 100 years of public holidays and updated the field that looks for the public holidays to look at the new range. I think it now goes up to column DO.

 

[Dave Patton]

If you are calculating holidays for the next 100 years, you may have to edit your formula for Easter.

There is a post on this forum that shows a correction as well as a UDF.