Date 10th of Each Month

[jwbanks10]

I have a spreadsheet that calculates the future due date of projects. The project is due on the 10th of each month but has to account for weekends and holidays. If a holiday (handled by the vlookup) or weekend is present is shows the previous workday.

The below formula works well, however, it only shows the due date in the current month. For example, if today is 09/11 it will say it is due 09/10 until October. I need it to say 10/10 as soon as 09/10 passes. Any ideas? Thanks!

=IFERROR(VLOOKUP(WORKDAY(EOMONTH($G$5,-1)+10+1,-1),Holidays!C:D,2,FALSE),WORKDAY(EOMONTH($G$5,-1)+10+1,-1))

 

[CyrusTheVirus]

Hi jw,

Could you post an example of your spreadsheet of how you want it to look and operate, along with your holiday schedule?

 

[jwbanks10]

Of course, here you go CyrusTheVirus. It is hard to explain via chat, but hopefully my explanation makes sense. All green cells mean they are correct. The orange cells represent the issue. Based on today's date, the next due date should technically be 10/10 not 09/10 like it says. My formula above just needs tweaked a bit.

 

[CyrusTheVirus]

Hi jw,

Try the below setup. It has a few helper cells, that way we don't have to create some mega formula. Let me know if you think this will work for you.

Excel Workbook
	A	B	C	D	E	F
1	Timing	Hours	Next Update	*	Today's Date:	9/11/2016
2	10th each Month	2	10/10/2016	*	Next 10th:	10/10/2016
3	10th each Month	1	10/10/2016	*	Is Holiday?	No
4	10th each Month	2	10/10/2016	*	Nearest Workday	10/10/2016
5	10th each Month	5	10/10/2016	*	*	*
6	Monday	0.3	9/12/2016	*	*	*
7	Monday	3	9/12/2016	*	*	*
8	Monday	2	9/12/2016	*	*	*
9	Wednesday	2	9/14/2016	*	*	*
Due Date

Excel Workbook
	A	B	C	D
1	Holiday	Description	Date	Prior Workday
2	New Year's Day	first of January	1/1/2016	12/31/2015
3	Presidents' Day	third Monday in February	2/15/2016	2/12/2016
4	Memorial Day	last Monday in May	5/30/2016	5/27/2016
5	Independence Day	fourth of July	7/4/2016	7/1/2016
6	Labor Day	first Monday in September	9/5/2016	9/2/2016
7	Thanksgiving	fourth Thursday in Novemeber	11/24/2016	11/23/2016
8	Day after Thanksgiving	Day after Thanksgiving	11/25/2016	11/24/2016
9	Christmas	twenty fifth of December	12/25/2016	12/23/2016
Holidays

 

[CyrusTheVirus]

Hi jw,

Actually I prefer the below setup. I think the order is more accurate this way.

Excel Workbook
	A	B	C	D	E	F
1	Timing	Hours	Next Update	*	Today's Date:	9/13/2016
2	10th each Month	2	10/10/2016	*	Next 10th:	10/10/2016
3	10th each Month	1	10/10/2016	*	Is Weekend?	No
4	10th each Month	2	10/10/2016	*	Nearest Workday	10/10/2016
5	10th each Month	5	10/10/2016	*	Is Holiday?	No
6	Monday	0.3	9/12/2016	*	Official Due Date	10/10/2016
7	Monday	3	9/12/2016	*	*	*
8	Monday	2	9/12/2016	*	*	*
9	Wednesday	2	9/14/2016	*	*	*
Sheet1

Excel Workbook
	A	B	C	D
1	Holiday	Description	Date	Prior Workday
2	New Year's Day	first of January	1/1/2016	12/31/2015
3	Presidents' Day	third Monday in February	2/15/2016	2/12/2016
4	Memorial Day	last Monday in May	5/30/2016	5/27/2016
5	Independence Day	fourth of July	7/4/2016	7/1/2016
6	Labor Day	first Monday in September	9/5/2016	9/2/2016
7	Thanksgiving	fourth Thursday in Novemeber	11/24/2016	11/23/2016
8	Day after Thanksgiving	Day after Thanksgiving	11/25/2016	11/24/2016
9	Christmas	twenty fifth of December	12/25/2016	12/23/2016
Holidays