Date formula

[Pv488]

Hi,
I am struggling a bit to get my date formulas working. In a sense it is a 2 pt question.
I basically have a date of which I need to add X amount of days which may go into into the next month at times. Then if that target date after getting added x days is a weekend or holiday it needs to go to the next working day. I have been using the workday & net work days function but I am not getting the desired result.

Example: Item date is 3/13/2017 (A2). I want to add 30 days to it I would get 4/12/2017 using =DATE(year(A2),(MONTH(A2),DAY(A2))+30.
If I use =WORKDAY(A2,30,"Holiday Table Schedule") I get 4/25/2017 which is incorrect. The date function gives me the desired date but I need it to account for Holidays and weekends for other dates.

Additionally, if I am looking to get the 15th day of following month from a date and if that 15th day falls on weekend or Holiday it gets rounded to next full working day. Example here is starting date (B2) is 3/7/2017. =WORKDAY(OEMONTH(B2,0),15,"Holiday Schedule) = 4/24/2017. I need it to get me 4/17/2017 as the 15th day falls on Weekend so it should go to the 17th.

Thanks

 

[Joe4]

Re: Help with Date formula

Welcome to the Board!

I get 4/25/2017 which is incorrect. The date function gives me the desired date but I need it to account for Holidays and weekends for other dates.

=WORKDAY(A2,30,"Holiday Table Schedule")

Have you set up your Holiday Table Schedule?
Not that this will not work "Holiday Table Schedule". That is a string. This argument needs to be a range or named range. If it is a named range, it should not have double-quotes around it (which makes it a literal string).

See: https://www.techonthenet.com/excel/formulas/workday.php

 

[Pv488]

Re: Help with Date formula

Thank you for the welcome! and yes i should've clarified I did set up the schedule. I have a table list for 5 years of all holidays with their dates which I reference into the formula.

 

[Dave Patton]

Re: Help with Date formula

Excel 2010
	A	B	C
1			12-Apr-17
2	13-Mar-17	30	24-Apr-17
3			17-Apr-17
4
4c

Cell Formulas
Range	Formula
C1	=A2+30
C2	=WORKDAY(A2,B2,Hol)
C3	=WORKDAY(DATE(YEAR(A2),MONTH(A2)+1,14),1)

Named Ranges
Name	Refers To	Cells
Hol	='4c'!$I$2:$I$5

 

[Pv488]

Re: Help with Date formula

Excel 2010

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]12-Apr-17[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]13-Mar-17[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]24-Apr-17[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17-Apr-17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

4c​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=A2+30[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=WORKDAY(A2,B2,Hol)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=WORKDAY(DATE(YEAR(A2),MONTH(A2)+1,14),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


The C3 Formula worked terrific to get the 15th day of next month and if weekend or Holiday gave the next working date, just what I needed thank you.

Now I need to get the +30 days from date and then next working date if Holiday or Weekend Working.

 

[Dave Patton]

Re: Help with Date formula

Did you try the formula in C2?

Ensure you use the relevant range for your list of Holidays.
You can name the list of Holidays.

Excel 2010
	A	B	C
1			12-Apr-17
2	13-Mar-17	30	24-Apr-17
3			17-Apr-17
4
5			24-Apr-17
4c

Cell Formulas
Range	Formula
C1	=A2+30
C2	=WORKDAY(A2,B2,I2:I5)
C3	=WORKDAY(DATE(YEAR(A2),MONTH(A2)+1,14),1)
C5	=WORKDAY(A2,30,I2:I5)

 

[Joe4]

Re: Help with Date formula

As I said, if you actually used the formula:

[B]=WORKDAY(A2,30,"Holiday Table Schedule")[/B]

it will not work, as "Holiday Table Schedule" is not a valid range (it is a string).

 

[Pv488]

Re: Help with Date formula

I managed to figure out +30 days formula by using =WORKDAY(B2+30-1,1,$S$7:$T$15) The $S$7:$T$15 is the reference to the list of dates I have in a table format in my worksheet as seen below. [TABLE="width: 300"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]
NYSE Holidays[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]New Year’s Day[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]1/1/2018[/TD]
[/TR]
[TR]
[TD]Martin Luther King, Jr. Day[/TD]
[TD="align: right"]1/16/2017[/TD]
[TD="align: right"]1/15/2018[/TD]
[/TR]
[TR]
[TD]Washington’s Birthday[/TD]
[TD="align: right"]2/20/2017[/TD]
[TD="align: right"]2/19/2018[/TD]
[/TR]
[TR]
[TD]Good Friday[/TD]
[TD="align: right"]4/14/2017[/TD]
[TD="align: right"]3/3/2018[/TD]
[/TR]
[TR]
[TD]Memorial Day[/TD]
[TD="align: right"]5/29/2017[/TD]
[TD="align: right"]5/28/2018[/TD]
[/TR]
[TR]
[TD]Independence Day[/TD]
[TD="align: right"]7/4/2017[/TD]
[TD="align: right"]7/4/2018[/TD]
[/TR]
[TR]
[TD]Labor Day[/TD]
[TD="align: right"]9/4/2017[/TD]
[TD="align: right"]9/3/2018[/TD]
[/TR]
[TR]
[TD]Thanksgiving Day[/TD]
[TD="align: right"]11/23/2017[/TD]
[TD="align: right"]11/22/2018[/TD]
[/TR]
[TR]
[TD]Christmas Day[/TD]
[TD="align: right"]12/25/2017[/TD]
[TD="align: right"]12/25/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Pv488]

Re: Help with Date formula

Yes I tried but it just gives me +30 days not accounting if end date was holiday or weekend but I have managed to figure it out using some tweeks with your formulas and use =WORKDAY(B7+30-1,1,$S$7:$T$15). Thank you for help!