Formula Opposite of DAYS360?

[gjeberly]

Hi all,

I'm curious if there's a formula to do the opposite of DAYS360. That is, DAYS360 figures out the number of days between two dates on a 360 day basis. I want to make a formula that adds a number of days on a 360 day basis.

So if my starting date is 5/25/19 and I add 6 days, it should result in 6/1/19 instead of 5/31/19. Is this possible?

Thanks for any help.

 

[SupremeDr]

Just add 7 days instead of 6? Not sure what your question is. You can write a formula where the date is in cell A1 and then B1 =A1+7

 

[Eric W]

You could use the WORKDAY.INTL function to add days. Set every day is a weekday, then make a list of all the days that are the 31st of a month as holidays. Like this:

Book1
	K	L	M	N
1	25-May	31-May		31-Jan
2		1-Jun		31-Mar
3				31-May
4	27-Feb	5-Mar		31-Jul
5		5-Mar		31-Aug
6				31-Oct
7				31-Dec
Sheet1

Cell Formulas
Range	Formula
L1	=K1+6
L2	=WORKDAY.INTL(K1,6,"0000000",N1:N7)
L4	=K4+6
L5	=WORKDAY.INTL(K4,6,"0000000",N1:N7)

The problem is February, I'm not sure how to handle that.

 

[gjeberly]

You could use the WORKDAY.INTL function to add days. Set every day is a weekday, then make a list of all the days that are the 31st of a month as holidays. Like this:

	K	L	M	N

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]25-May[/TD]
[TD="align: right"]31-May[/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-Jan[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Jun[/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-Mar[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-May[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]27-Feb[/TD]
[TD="align: right"]5-Mar[/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-Jul[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5-Mar[/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-Aug[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-Oct[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-Dec[/TD]

</tbody>

Sheet1​

[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] "]L1[/TH]
[TD="align: left"]=K1+6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
[TD="align: left"]=WORKDAY.INTL(K1,6,"0000000",N1:N7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L4[/TH]
[TD="align: left"]=K4+6[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L5[/TH]
[TD="align: left"]=WORKDAY.INTL(K4,6,"0000000",N1:N7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The problem is February, I'm not sure how to handle that.

This might have to be my solution if there isn't anything built into Excel. February always causes problems with 360 day bases unfortunately.