Find nearest WORKDAY before a date

[psycoperl]

I would like to find the workday before a certain date.
Workdays are defined normally as Monday to Friday, but I would also like to account for holidays both standard and internal company holidays.

I have a list of dates in an excel sheet that are the internal company holidays.


For example December 25, 2024 is a Wednesday which is Christmas as we are closed. However due to Christmas Eve Dec 24 also being an internal holiday we are not open on that date either. So for December 25 the nearest workdate is December 23, 2024.

Another example is Feb 19, 2024 was Presidents Day (US) which was a Monday, so the nearest work day is Friday, Feb 16, 2024.

 

[Fluff]

How about

=workday(a2+1,-1,Holidays!a2:a20)

Where A2 is the date & holidays is your list of holidays

 

[psycoperl]

How about

=workday(a2+1,-1,Holidays!a2:a20)

Where A2 is the date & holidays is your list of holidays

Hi Fluff - Using your formula I got a #VALUE! Error

Book1
	A	B	C		E		G	H	I	J	K	L
1	Date	Time						ExcelDateTime	Is During EDT	Day Of Week
2	Wednesday, March 6, 2024	9:30:00 AM						3/6/2024 9:30	FALSE	3		#VALUE!
3	Wednesday, March 6, 2024	11:30:00 AM						3/6/2024 11:30	FALSE	3		#VALUE!
4	Wednesday, March 6, 2024	1:30:00 PM						3/6/2024 13:30	FALSE	3		#VALUE!
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=A2+B2
I2:I4	I2	=MATCH(A2,DATE(YEAR(A2),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(A2),{1,3,11},7)))=2
J2:J4	J2	=WEEKDAY(A2,11)
L2:L4	L2	=WORKDAY(A2+1,-1,Holidays!$A$1:$A$200)

Book1
	A
1	HolidayList
2	9/4/2023
3	10/9/2023
4	11/7/2023
5	11/10/2023
6	11/11/2023
7	11/23/2023
8	11/24/2023
9	12/24/2023
10	12/25/2023
11	12/29/2023
12	12/30/2023
13	12/31/2023
14	1/1/2024
15	1/15/2024
16	2/12/2024
17	2/19/2024
18	3/29/2024
19	5/27/2024
20	6/19/2024
21	6/28/2024
22	7/4/2024
23	7/12/2024
24	7/19/2024
25	7/26/2024
26	8/2/2024
27	8/9/2024
28	8/16/2024
29	10/14/2024
30	11/11/2024
31	11/28/2024
32	11/29/2024
33	12/24/2024
Holidays

 

[etaf]

how about
=WORKDAY(A2-1,1,$E$2:$E$12)
where E2:E12 are the holidays and internal dates

Book20
	A	B	C	D	E
1		workday			Holidays
2	2/1/24	2/1/24			2/5/24
3	2/2/24	2/2/24
4	2/3/24	2/6/24
5	2/4/24	2/6/24
6	2/5/24	2/6/24
7	2/6/24	2/6/24
8	2/7/24	2/7/24
9	2/8/24	2/8/24
10	2/9/24	2/9/24
11	2/10/24	2/12/24
12	2/11/24	2/12/24
13	2/12/24	2/12/24
14	2/13/24	2/13/24
15	2/14/24	2/14/24
16	2/15/24	2/15/24
17	2/16/24	2/16/24
18	2/17/24	2/19/24
19	2/18/24	2/19/24
20	2/19/24	2/19/24
21	2/20/24	2/20/24
22	2/21/24	2/21/24
Sheet1

Cell Formulas
Range	Formula
B2:B22	B2	=WORKDAY(A2-1,1,$E$2:$E$12)

 

[Fluff]

It needs to be

=WORKDAY(A2+1,-1,Holidays!$A$2:$A$200)

as you have text in A1 on the holiday sheet.

 

[psycoperl]

It needs to be

=WORKDAY(A2+1,-1,Holidays!$A$2:$A$200)

as you have text in A1 on the holiday sheet.

ahh -- I got it working with =WORKDAY(A2,-2,Holidays!$A$2:$A$300)

 

[Fluff]

That will return a Monday if the start date is Wednesday, is that what you wanted?