Too many arguments and random days for months

[drt80]

Hello, Newbie here,

I am trying to create a spreadsheet, and I have it setup for the most part, but needing some help. The 12, 24 & 36 Mo. tabs work the same. The only difference is the size of the Gray Table. In the link, there is a downloadable version of the workbook.

https://techcommunity.microsoft.com...-on-figuring-days-remaining/m-p/652001#M31790


I have it set up so when the workbook is first opened it will be blank until a date is manually entered in Cell B6. Once that is populated, the Gray Table will fill in all the required info pulling from the Sheet1 Tab based on what month it is. All of the info. on Sheet1 is based on historical data.

What I am trying to do is, in Cell C19 have this formula =IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,4,FALSE),(EOMONTH(A19,0)-A19)-NETWORKDAYS(A19,EOMONTH(A19,0)))+1. When I input that, I get an error saying" too many arguments for this function". Is there a way around this?

In Cell D19, I am needing it to figure based on what month it’s from Cell A19 how many Rain Days are left for the month based on what day of the month it is. Let us use the month of May. In May, there is 2 historical rain days for the month, to break up these days 1-rain days can be used for the 1st to the 15th, and the 2nd rain day can be used for the 16th to the 31st. If the date in Cell A19 is 5/23/2019, then the value in Cell D19 should be 1. If it is 5/7/2019 then the value in Cell D19 should be 2. The month in Cell in A19 will be different based on when Time Charges Began (Cell B6). I need something that will work in the Cell Range D19:F34. Is this possible?

 

[Special-K99]

Re: Too many arguments and random days for months help.

Your problem is the IF statement.

An IF statement consists of a

condition,
statement if true,
statement if false

You have

=IF(LEN(A19)=0, condition
"", statement if true
VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,4,FALSE), statement if false

(EOMONTH(A19,0)-A19)-NETWORKDAYS(A19,EOMONTH(A19,0)) this is superfluous and is causing the error
)+1 then you have this, if the condition is true result is "" - you cant add 1 to a string

I didn't read any further as your description sounds complicated.

 

[drt80]

Re: Too many arguments and random days for months help.

Any suggestions on how to simplify it?

 

[Snakehips]

Re: Too many arguments and random days for months help.

@drt80

Maybe with respect to your second part, which I assume is to apportion Rain Days to month 1 when it is a partial month.....
Having a couple of helper cells, in columns you might hide, will make the formula look less intimidating.
Excel 2010

	Q	R

<tbody>
[TD="align: center"]17[/TD]
[TD="align: center"]Month 1[/TD]
[TD="align: center"]Month 1[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]Max Cal Days[/TD]
[TD="align: center"]Max Rain Days[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]2[/TD]

</tbody>

12 MO. Road​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]Q19[/TH]
[TD="align: left"]=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,3,FALSE))[/TD]
[/TR]
[TR]
[TH]R19[/TH]
[TD="align: left"]=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,5,FALSE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Then for cell D19...
Excel 2010

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]15[/TD]
[TD="align: center"]Zone 3 - Roadway[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Calendar[/TD]
[TD="align: center"]Weekend[/TD]
[TD="align: center"]Rain[/TD]
[TD="align: center"]Cold[/TD]
[TD="align: center"]Wet[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Available[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Holidays[/TD]
[TD="align: center"]Work Days[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]16/05/2019[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]

</tbody>

12 MO. Road​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D19[/TH]
[TD="align: left"]=IF(LEN(A19)=0,"",IF(DAY(A19)<=(Q19/R19),R19,IF(DAY(A19)<=(Q19/R19*2),R19-1,IF(DAY(A19)<=(Q19/R19*3),R19-2,R19-3))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

As is, the formula deals with a maximum of 4 rain days per month.

Hope that helps.

 

[Snakehips]

Re: Too many arguments and random days for months help.

I'm suspecting that you might want the following for C19. ????
b>Excel 2010</b>

Book1
	A	B	C	D	E	F	G	H
15	Zone 3 - Roadway
16		Calendar	Weekend	Rain	Cold	Wet		Available
17	Month	Days	Days	Days	Days	Days	Holidays	Work Days
18
19	15/05/2019	16	4	2	0	-1	1	10
12 MO. Road

Cell Formulas
Range	Formula
C19	=IF(LEN(A19)=0,"",(EOMONTH(A19,0)-A19)-NETWORKDAYS(A19,EOMONTH(A19,0)))+1

 

[drt80]

Re: Too many arguments and random days for months help.

That is what I was wanting. Thank you very much. When I try to use the formula to columns E & F and I change the corresponding columns I get the error #DIV/0!. I get that because the answer is 0. Where in the formula can i place the IFERROR command? Also, in the Holidays column (G) is there a formula that excel know would based on the Day of the Month in Cell A19 that the holiday has passed? Ex. In July, there is one Holiday. if the date is July 5th or greater then the holiday in column G would go to 0.

@drt80

Maybe with respect to your second part, which I assume is to apportion Rain Days to month 1 when it is a partial month.....
Having a couple of helper cells, in columns you might hide, will make the formula look less intimidating.
Excel 2010

	Q	R

<tbody>
[TD="align: center"]17[/TD]
[TD="align: center"]Month 1[/TD]
[TD="align: center"]Month 1[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]Max Cal Days[/TD]
[TD="align: center"]Max Rain Days[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]2[/TD]

</tbody>

12 MO. Road​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]Q19[/TH]
[TD="align: left"]=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,3,FALSE))[/TD]
[/TR]
[TR]
[TH]R19[/TH]
[TD="align: left"]=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,5,FALSE))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Then for cell D19...
Excel 2010

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]15[/TD]
[TD="align: center"]Zone 3 - Roadway[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Calendar[/TD]
[TD="align: center"]Weekend[/TD]
[TD="align: center"]Rain[/TD]
[TD="align: center"]Cold[/TD]
[TD="align: center"]Wet[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Available[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]Month[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Days[/TD]
[TD="align: center"]Holidays[/TD]
[TD="align: center"]Work Days[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]16/05/2019[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]

</tbody>

12 MO. Road​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D19[/TH]
[TD="align: left"]=IF(LEN(A19)=0,"",IF(DAY(A19)<=(Q19/R19),R19,IF(DAY(A19)<=(Q19/R19*2),R19-1,IF(DAY(A19)<=(Q19/R19*3),R19-2,R19-3))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

As is, the formula deals with a maximum of 4 rain days per month.

Hope that helps.

 

[Snakehips]

Re: Too many arguments and random days for months help.

Regarding the IFERROR, try like.......

[COLOR=#333333][I]
[/I][/COLOR]=IF(LEN(A19)=0,"",IFERROR(IF(DAY(A19)<=(Q19/R19),R19,IF(DAY(A19)<=(Q19/R19*2),R19-1,IF(DAY(A19)<=(Q19/R19*3),R19-2,R19-3))),0))

And for G19......

=IF(LEN(A19)=0,"",SUMPRODUCT((YEAR(Holidays!E2:R13)=YEAR(A19))*(MONTH(Holidays!E2:R13)=MONTH(A19))*(Holidays!E2:R13>=A19)))

 

[drt80]

Re: Too many arguments and random days for months help.

The Holidays formula works great!! The IFERROR works too, but I moved it to Cell F19 and changing the reference cells and its throwing some of the numbers off. Like June is now saying it has -4 Wet days. Am I wrong for thinking the formula in Cells D19:F19 could be used? Or does Cell F19 need a different formula? Thanks for your help!

 

[drt80]

Re: Too many arguments and random days for months help.

The Holidays formula works great!! The IFERROR formula works too, but i moved it to Cell F19 and changed the referencing cells. It has messed with some of the number in column F. Take June, it is now saying there are -4 Wet days. Am I wrong for thinking I could just move the formula to the next cell and it would work? Or do I need a different formula for Column F? Thanks for all your help!

 

[Snakehips]

Re: Too many arguments and random days for months help.

I think if you set up the helper cells as....

Excel 2010
	Q	R	S	T
17	Month 1	Month 1	Month 1	Month 1
18	Max Cal Days	Max Rain Days	Max Cold Days	Max Wet Days
19	31	3	0	-2
12 MO. Road

Cell Formulas
Range	Formula
Q19	=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,3,FALSE))
R19	=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,5,FALSE))
S19	=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,6,FALSE))
T19	=IF(LEN(A19)=0,"",VLOOKUP(MONTH(A19),Sheet1!$A$3:$H$14,7,FALSE))

Then the following formula in D19 will copy accros to E19 and F19
I have amended it to hopefully to deal with the possibility of negative numbers.

Excel 2010
	A	B	C	D	E	F	G	H
19	16/08/2020	15	5	2	0	-1	0	9
12 MO. Road

Cell Formulas
Range	Formula
D19	=IF(LEN($A19)=0,"",IFERROR(IF(DAY($A19)<=($Q19/ABS(R19)),R19,IF(DAY($A19)<=($Q19/ABS(R19)*2),MIN(ABS(R19)-1,R19+1),IF(DAY($A19)<=($Q19/ABS(R19)*3),MIN(ABS(R19)-2,R19+2),MIN(R19-3,R19+3)))),0))
E19	=IF(LEN($A19)=0,"",IFERROR(IF(DAY($A19)<=($Q19/ABS(S19)),S19,IF(DAY($A19)<=($Q19/ABS(S19)*2),MIN(ABS(S19)-1,S19+1),IF(DAY($A19)<=($Q19/ABS(S19)*3),MIN(ABS(S19)-2,S19+2),MIN(S19-3,S19+3)))),0))
F19	=IF(LEN($A19)=0,"",IFERROR(IF(DAY($A19)<=($Q19/ABS(T19)),T19,IF(DAY($A19)<=($Q19/ABS(T19)*2),MIN(ABS(T19)-1,T19+1),IF(DAY($A19)<=($Q19/ABS(T19)*3),MIN(ABS(T19)-2,T19+2),MIN(T19-3,T19+3)))),0))
G19	=IF(LEN(A19)=0,"",SUMPRODUCT((YEAR(Holidays!E2:R13)=YEAR(A19))*(MONTH(Holidays!E2:R13)=MONTH(A19))*(Holidays!E2:R13>=A19)))

Right..... that's me done....