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.

[TABLE="width: 180"]
<tbody>[TR]
[TD]labour day[/TD]
[TD]First Monday in October
[/TD]
[/TR]
[TR]
[TD]New years day[/TD]
[TD]First day in january

[/TD]
[/TR]
[TR]
[TD]australia day[/TD]
[TD="align: right"]26-Jan[/TD]
[/TR]
[TR]
[TD]Easter Sunday[/TD]
[TD]
??? Unsure how to calculate
[/TD]
[/TR]
[TR]
[TD]Easter Saturday[/TD]
[TD]Day before easter Sunday
[/TD]
[/TR]
[TR]
[TD]Easter Friday[/TD]
[TD]2 Days before easter Sunday
[/TD]
[/TR]
[TR]
[TD]Easter Monday[/TD]
[TD]Day after Easter Sunday
[/TD]
[/TR]
[TR]
[TD]Anzac Day[/TD]
[TD]
25th aprill

[/TD]
[/TR]
[TR]
[TD]Queen's birthday[/TD]
[TD]Second Monday in June
[/TD]
[/TR]
[TR]
[TD]labour day[/TD]
[TD]First Monday in October
[/TD]
[/TR]
[TR]
[TD]Christmas Day[/TD]
[TD="align: right"]25-Dec
[/TD]
[/TR]
[TR]
[TD]Boxing Day[/TD]
[TD="align: right"]


26-Dec

​

[/TD]
[/TR]
</tbody>[/TABLE]
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