Day of week

[dpaton05]

Is there a function you can put in a cell that will calculate whether a date that is entered in another cell is a weekday, weekend or public holiday?

 

[wideboydixon]

You'd have to provide a list of public holidays; Excel doesn't store that information. Once you have them then it's relatively simple:

Book1
	A	B	C	D
1	Public Holidays		Date	Type of day
2	01/01/2018		01/05/2018	Weekday
3	30/03/2018		02/05/2018	Weekday
4	02/04/2018		03/05/2018	Weekday
5	07/05/2018		04/05/2018	Weekday
6	28/05/2018		05/05/2018	Weekend
7	27/08/2018		06/05/2018	Weekend
8	25/12/2018		07/05/2018	Public Holiday
9	26/12/2018		08/05/2018	Weekday
10			09/05/2018	Weekday
11			10/05/2018	Weekday
12			11/05/2018	Weekday
13			12/05/2018	Weekend
14			13/05/2018	Weekend
15			14/05/2018	Weekday
16			15/05/2018	Weekday
17			16/05/2018	Weekday
18			17/05/2018	Weekday
19			18/05/2018	Weekday
20			19/05/2018	Weekend
21			20/05/2018	Weekend
22			21/05/2018	Weekday
23			22/05/2018	Weekday
24			23/05/2018	Weekday
25			24/05/2018	Weekday
26			25/05/2018	Weekday
27			26/05/2018	Weekend
28			27/05/2018	Weekend
29			28/05/2018	Public Holiday
30			29/05/2018	Weekday
31			30/05/2018	Weekday
32			31/05/2018	Weekday
Sheet1

Cell Formulas
Range	Formula
D2	=IF(ISNUMBER(MATCH($C2,$A$2:$A$9,0)),"Public Holiday",IF(WEEKDAY($C2,2)>5,"Weekend","Weekday"))

WBD

 

[dpaton05]

I have a list of public holidays.

labour day	First Monday in October
New years day	First day in january
australia day	26-Jan
Easter Sunday	??? Unsure how to calculate
Easter Saturday	Day before easter Sunday
Easter Friday	2 Days before easter Sunday
Easter Monday	Day after Easter Sunday
Anzac Day	25th aprill
Queen's birthday	Second Monday in June
labour day	First Monday in October
Christmas Day	25-Dec
Boxing Day	26-Dec ​

<tbody>
</tbody>

I am trying to make a spreadsheet that will be used to generate quotes for my work. The quotes will differ in price, depending on whether the date is a weekday, weekend or public holiday. Could anyone please help me with a function that I can enter into a cell for the start of my total function to go in my quote price cell?

Thanks,
Dave